Sorting anomalies with mixed numbers & text

[Anneka_K]

I’d like to be able to copy & paste columns from an excel spreadsheet report into my Airtable Base, but I’m finding it hard to get the sorting to match. I need to sort by ISBN10, which can contain both numbers and text. Excel and Airtable apparently have different ideas about how such things should be sorted.

I’ve had to create a workaround where I sort my Excel list, number each ISBN according to the excel sort order, copy those cells into an Airtable sheet, re-sort, and add another column numbering the the list according to the Airtable sort. I then have a proxy for each sorting order and can copy and paste the data needed. (example below)

Not a big deal to do once, but untenable with frequent updates to the list.

Is there any workaround people have found to get Airtable to sort like Excel (or vice versa) ?

Thanks.

(PS: I know there are 2 options for sorting mixed cells in excel, but since neither of them match Airtable sorting, I’ve omitted that part of my experiment for simplicity’s sake)

[John_Vagg]

The sorting algorithm in Air Table is wrong
It sorts 1,2,3,4,5,6,7,8,9,A,a,B,b,C,c in this order (lower case a before B)?
Then if you put a 1 in front of each character you would think the sort would be the same. No!
1A,1a,1B,1b,1C,1c,11,12,13,14,15,16,17,18,19
Change the 1 for an a
a1,a2,a3,a4,a5,a6,a7,a8,a9,aA,aa,aB,ab,aC,ac
Then make some of them 4 characters by adding an extra 1 at the beginning and end
1a2,1a4,1a6,1a8,1a11,1a31,1a51,1a71,1a91,1aA,1aa1,1aB,1ab1,1aC,1ac1

Some weird logic there.