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 this?
Assuming your data is in column A, put this formula in column B (or wherever convenient) 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.
, , , , , ( , .
, a, : =VALUE(RIGHT(A1,LEN(A1)-1)).
a
=VALUE(RIGHT(A1,LEN(A1)-1))
, :
=1*MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$9),1)),0),COUNT(1*MID(A2,ROW($1:$9),1)))
, .. Enter, Ctrl - Shift - Enter.
go .
- , . .