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!