Thread: Alphanumeric sorting

Hello, I need to sort a column of data that's alphanumeric. It's the volume numbers for my library's *** collection

It's the following type of data: [Letter for genre][number from 1-999][occasional suffix letter]

I'd like it to sort like this:
A1
A2
A3
A3a
A3b
A4
...
A157
B1
B2
...
etc.

Unfortunately, right now it's sorting like this:
A1
A10
A100
A101
...

I saw another post that solved this same problem by inserting zeroes before the 1 and 2 digit numbers. Unfortunately, I've got about 1400 cells, so manually doing that is not an option.

I'm pretty low on skill when it comes to formulas and programming, so a solution that would be simple to implement would be preferred. Thanks!

Assuming those strings are in column A starting at A1:

1) Put this formula in an empty column and copy down:
=IF(ISNUMBER(RIGHT(A1)+0), LEFT(A1,1) & TEXT(MID(A1,2,LEN(A1)),"000"), LEFT(A1,1) & TEXT(MID(A1,2,LEN(A1)-2),"000") & RIGHT(A1))

2) Highlight all the formulas you created

3) Ctrl-C to copy them

4) Select Edit > Paste Special > Values

5) Now copy the values again and paste them over the original values in column A.

6) Remove the values from the column you added

7) You can now sort the data by the newly created standardized text strings.