Sorting anomalies with mixed numbers & text


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) ?


(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)


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!
Change the 1 for an a
Then make some of them 4 characters by adding an extra 1 at the beginning and end

Some weird logic there.