Sorting numbers and letters

You'll need helper cells to apply a consistent format to your data if you want it to sort consistently.

With data in column A:

1) B1: =SUBSTITUTE(A1,LOOKUP(99^99,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(A$1:INDEX(A:A,LEN($A1))))),"")
2) C1: =TEXT(LOOKUP(99^99,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(A$1:INDEX(A:A,LEN($A1))))),"000")&B1
3) Now highlight all 3 columns
4) sort by column C
5) Remove B:C

Hi,

Can you clarify your comment, "it only changes the column and not rows". I don't understand.
What exactly are you trying to sort. You're using the .CurrentRegion property anchored on A1. Are you sure there's a contiguous array of values across the columns and down the rows?

If you're going VBA, you can use the method I already gave in post #2 and record that into a macro.

Sometimes it is easier to change 1a into 01a

Originally Posted by popipipo

Sometimes it is easier to change 1a into 01a

Yes, that's exactly what the instructions in post #2 provide, a consistent number of digits for all the numbers.