I have things numbered by letter and number (ex. A1, A2, A3, etc). Once I get to a set that has a number beyond 9, it will not go in order. So for example, when I hit D it has a D10, D11, etc., and those go right after 1.

Start with A01, A02, A03 etc

Numbers is sorting correctly but not naturally. I suggest adding a special sort column to make this easier:

The column labeled "Sort ID" is the new column.

B2=IF(A2="","",LEFT(A2, 1)&NUMTOBASE(RIGHT(A2,LEN(A2)−1),10,4))

this is shorthand for... select cell B2, then type (or copy and paste from here) the formula:

=IF(A2="","",LEFT(A2, 1)&NUMTOBASE(RIGHT(A2,LEN(A2)−1),10,4))

select cell B2, copy

select cells B2 thru the end of the column, paste

now sort using column B (instead of column A)

Hi Jessica,

When you join a number to a text string, the whole thing becomes a text string, and is sorted using the rules for text.

A1 will sort before A2 because the first character is the same in each, and the second character is different and 1 is sorted alphabetically before 2.

A19 will also sort before A2 for exactly the same reason: the first character (A) is the same in each, the second (1 or 2) is different, and 1 comes alphabetically before 2.

The order is decided at that point (just as the alphabetical order between "any" and "at" is decided without reference to the "y" in "any"—nothing is considered after the first character pair that is not a match.

Csound and Wayne have both offered a solution: add enough leading zeroes to the number to make the numeric part of every alphanumeric 'number' the same.

That works well, provided you can plan far enough ahead to predict the largest number part you will need.

Csound's single leading zero will work for numeric parts up to 99 (with the zeroes added only to the first nine numbers in any set), then fail at 100.

Wayne's suggestion of starting with 3 leading zeroes (making all the number parts four digits) will take to from 0001 to 9999 before failing, and because it's done with a formula, is easier to edit if you need to go to longer numbers.

Regards,

Barry

Barry,

Good note regarding the number of digits. I made a conscious decision to omit that detail and settled on 4 digits to balancing leaving enough room without explaining everything.

Since you opened this particular facet of my proposed solution....

The number of digits is controlled by the "4" at the end of the formula (bolded):

B2=IF(A2="","",LEFT(A2, 1)&NUMTOBASE(RIGHT(A2,LEN(A2)−1),10,4))

Also... since Numbers only allow 65535 entries in a table 4 digits would mean about 6 different letters from 0 to 9999