Excel sort numbers with letter prefix

Question

I have a column:

a1
a10
a11
a12
a13
a14
a15
a16
a17
a18
a19
a2
a20
a21
a22
a23
a24
a25
a26
a27
a28
a29
a3
a30
a31
a4
a5
a6
a7
a8
a9

But I need to sort it like this:

a1 a2 a3 a4 a5 a6 a7 a8 a9 a10 a11 a12 a13....

Does anyone know how to do it?

Answer 1

Assuming your data is in column A, put this formula in column B (or wherever is handy) and sort by it:

=LEFT(A1,1) & TEXT(SUBSTITUTE(A1,LEFT(A1,1),""),"00")

This assumes that you have one character in each cell in column A, followed by a number.

Answer 2

I'm not aware of an option that allows you to sort the way you like - however, you could help yourself with a a helper column that extracts the number - and then sort both columns by this column (and delete the helper column afterwards if you wish.

Assuming your text would always be a, you could extract the number with this formula: =VALUE(RIGHT(A1,LEN(A1)-1)).

In case you have different strings, use this formula:

=1*MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$9),1)),0),COUNT(1*MID(A2,ROW($1:$9),1)))

You need to enter it as an array formula, i.e. instead of pressing Enter, press Ctrl-Shift-Enter.

Credits for the latter formula go here.