Sorting Numbers in Text Fields: Unexpected Behavior

[Matt_Kennedy1]

Here’s how Airtable is sorting my text col:
2XY
11A
This logic seems to be unique to Airtable: 11 is after 2 (not 1 is before 2, as expected). This causes problems trying to match a sorted list in Airtable with the same list sorted in another program.

Is this a bug, intended behavior that can be worked around, or something else?

Thanks!

[Justin_Barrett · Accepted solution]

It’s actually sorting logically, but the logic may not be clear based on those two items alone. I added those to a table along with some other items, and I think the pattern might be more clear after seeing this:

Long story short, it’s sorting by number first, then by the characters after the number (if any are present). It’s not sorting character by character.

A simple way to force the sort to go character by character is to put spaces between each character. These are the same items with the same sort settings:

Thankfully you don’t need to apply this change manually. This formula will create the desired output, and you can then sort by the formula field (which can be hidden).

REGEX_REPLACE({Your Field Here}, "(.)", "$1 ")

Here’s the original data in the left column, and the formula output in the right, with the sort applied to the formula field.

[Matt_Kennedy1]

Great solution!

I do understand that the sort is logical, but I am surprised that this logic does not conform to standard expectations. Perhaps this was an idea dreamed up as a cool feature (autodetect numerical series in strings and treat them as numbers). But since I do a lot of moving data back and forth between apps, it is cumbersome to account for two different sorting paradigms. I like your formula option. Didn’t know Airtable had Regex - Cool!

[Matt_Lusty]

A) Its only "sort of" sorting logical... as it's not logical to not conform to standards!

B) Plus if they really thought this was a "cool new feature" that detects strings of numbers (instead of character by character) they would have extended that to a decimal string, because its wrecking my ability to sort my decimals (see example in picture below).

(I don't want to have to include insignificant decimal places as with zeroes... ie "1.10" and "1.30").

(And I also want to suffix each number with "mph" text, so I have to use the Text field)

(I don't want to add complications to the table, by having to patch it with additional formula fields, which then also separates editability and viewability, and duplicates where this simple data is stored (one for viewing, one for editing)...

In summary they should either A) conform to sorting standards, or B) go the full mile with their "special" number-string detection, to be smart enough to include decimals.