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

# Sorting Text Fields Containing Both Text

### 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
•
•
• 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

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

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").