Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here
Sep 28, 2021 08:00 AM
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!
Solved! Go to Solution.
Sep 28, 2021 11:17 AM
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.
Sep 28, 2021 11:17 AM
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.
Sep 28, 2021 03:21 PM
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!