2 Replies Latest reply on May 30, 2015 2:24 PM by walrus28

    Sorting Text Fields Containing Both Text

    walrus28

      Title

      Sorting Text Fields Containing Both Text & Numbers

      Post

      I have a text field called item_name that contains names on product packaging.

      I would like to sort my records based on this field. I would like it to sort as text, but to sort numbers as numbers, even when the numbers are part of a string of text. 

      Numbers can be present at the beginning, middle, end, or not at all. 

      Here are some sample entries in the order I would like to sort them.

             
      • 5/1 Pens
      •      
      • 24/7
      •      
      • 80 Dogs
      •      
      • 500 Dogs
      •      
      • 8,000 Dogs
      •      
      • Comin' @ You
      •      
      • Comin' 4 You
      •      
      • Cool Train 9
      •      
      • Cool Train 387
      •      
      • Pizza
      •      
      • Real 70's Big
      •      
      • Real 70's Small
      •      
      • Real 600's

       

      (I made up the names, but they follow the same patterns as the names actually in my field.)

        • 1. Re: Sorting Text Fields Containing Both Text & Numbers
          raybaudi

          I think that this calculation could solve 90% of your sorting problems. Sort by it:

          Let([
          text = item_name ;
          dec = Left ( 1/2 ; 1 ) ;
          F =  Filter ( text ; "1234567890/" & dec ) ;

          S1 = Case (
          PatternCount ( F ; "/" ) ; Left ( F ; Position ( F ; "/" ; 1 ; 1 ) - 1 ) ;
          PatternCount ( F ; dec ) ; Left ( F ; Position ( F ; dec ; 1 ; 1 ) - 1 ) ;
          F
          ) ;

          S2 = Case (
          PatternCount ( F ; "/" ) ; Middle ( F ; Position ( F ; "/" ; 1 ; 1 ) + 1 ; Length ( F ) ) ;
          PatternCount ( F ; dec ) ; Middle ( F ; Position ( F ; dec ; 1 ; 1 ) + 1 ; Length ( F ) )
          )

          ];
          Substitute ( text ; F ; If ( S1 ; Right ( "00000000" & S1 ; 8 ) ) & If ( S2 ; Right ( "00000000" & S2 ; 8 ) ) )
          )

          • 2. Re: Sorting Text Fields Containing Both Text & Numbers
            walrus28

            Thanks raybaudi.

            This work well for the example I gave, but when I tested it on my data, I realized that I also have names that contain multiple numbers. 

            Some of my products have names which may contain up to 5 different numbers (ie. "55 word 2045 more words 442 more works 204 more words 305").