MS Excel - Sorting text cells

I am entering stamp collection catalogue "numbers" in a spreadsheet column. However, I am having problems sorting the stamps properly. For example: stamps 1, 2, 3, 1a, 2a, 3a should be sorted as 1, 1a, 2, 2a, 3, 3a. I know I am doing something wrong because I keep getting the original: 1, 2, 3, 1a, 2a, 3a. Could someone please give me a hint or two at how to go about sorting my "numbers" properly? Thanks in advance.

1. Insert a blank column next to the one with your numbers 1,1a etc
2. In that new column use a formula which is
   Code:

   =""&A2

   where A2 is the cell reference of the first value in the catalogue numbers column (in my example it's in the first column)
3. copy that formula down to all rows
4. Add a title to the top of the new column, eg. 'Sorter'
5. Select all the data range, and from the Data bit of the Ribbon, select the Sort option (not using an Autofilter), and select to sort on the new Sorter column
6. You should get a dialog popping up saying the sort key may not sort as expected because it contains some numbers formatted as text. In this, choose the option to 'Sort numbers and numbers stored as text separately'.

This should sort in the order 1,1a,1b,2,2a etc.

I regret that when you go into double digits your solution will provide: 1, 10, 11, 12 .......1a, 2, 20, 21, .... 2a, etc.

I've had a similar problem with other things and the sort feature but mostly was solved by adding a 0 at the beginning of the number or maybe 2 0s if it was a long list. Seems Windows or Office likes to sort by number, number & letter, 2 numbers, 2 numbers & letter, etc. I got a nice little free program from Organizer Software by Duck Software for our church's library cataloging which can export as a .csv file, they have one for Stamps.

Tony Vella said:

I regret that when you go into double digits your solution will provide: 1, 10, 11, 12 .......1a, 2, 20, 21, .... 2a, etc.

Yes I see your point. It's quite fiddly to separate numbers out of a string with a mix of numbers and letters.

Is there always just one letter at the end? Eg. 23k but not 27aa?

If there's just one letter then something like this formula in the 'Sorter' column might work?

Code:

=IF(ISNUMBER(0+RIGHT(A3,1)),TEXT(A3,"00000")&"_",TEXT(LEFT(A3,LEN(A3)-1),"00000")&RIGHT(A3,1))

It's along the lines of Berton's comment of adding 0s but by putting a non-numeric _ character at the end when there's no letter, it should stay as a non-numeric value so no danger of it losing the leading zeros.

However this won't work if you ever have multiple letters at the end (eg. 27ab).

It's a feature, see MS description and attempted solutions here:
https://support.microsoft.com/en-us/...-data-in-excel
Worksheet design needs to comply with excel features.
For stamps you might need several columns such as country, some subdivision, year, something about the stamp. Follow with multicolumn sort.
Now, if it really is of the 1,2,1a,… type, AND if only one lower case letter is used then this mad formula converts letters of what's in column A to a number after a decimal point.
Multiply makes it a number otherwise excel might still treat it as text.
IF(ISNUMBER($A2),$A2,LEFT($A2,LEN($A2)-1)&"."&CODE(RIGHT($A2,1))-96)
Just couldn't resist joining the fun of playing with it :)