Challenged Alphanumeric Sorting

[gadie]

Hi all,

I'm new to this forum. Generally i'm learning by reading others problems but, this time i couldn't find the answer.
The thing is i'm trying to sort an alphanumeric column with different length size of letters and numbers.
Here an example:
a1
a10
a12
a2
bb1
bb10
bb2
bb4

The column should be sorted as:
a1
a2
a10
a12
bb1
bb2
bb4
bb10

I rather it to be without VBA unless, there is no other choice.

Thanks

 

[ukmikeb]

Hi

Welcome to the forum.

Try :-

=LEFT(A1,FIND(LOOKUP(9.99999999999999E+307, MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))+0),A1)-1)&TEXT(LOOKUP(9.99999999999999E+307, MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))+0),"0000")

this in a spare column, assuming your data starts in cell A1 otherwise change accordingly and drag down.

I assume also that the numbers go up to 9999.

Sort on the spare column that you have entered the formula in.

hth

 

[gadie]

Thank you Mike. It sure works for me.
Now i'll add some automation to implement on any range with Input box.

Thanks again.

 

[ukmikeb]

Hi

Thanks for the feedback.

Pleased to have help solve your problem.

Good luck with your project.