Complex alphanumeric sorting

**poetstorm** · 12-29-2010, 10:57 AM

Hi there, new here. :D

I've read through some of the other alphanumeric sorting posts but nothing seemed to help with my problem. I have something like this:

a1402
a1a21
a1235
z124
x1x2
x11a2

Not only are the letters and numbers mixed, but also of different lengths.

What it should sort as is:

a1a21
a1235
a1402
x1x2
x11a2
z124

But what it is doing is this:

a1235
a1402
a1a21
x11a2
x1x2
z124

aia should be BEFORE a12 and a14 etc...

I can make the cells all the same length by inserting numbers at the end but I can't get it to sort this right. Assuming I have hundreds more rows in my project so it's not so simple as manually reorganizing, is there anything I can do? Thnks!

**DonkeyOte** · 12-29-2010, 11:04 AM

Is the "prefix" always a single character or can it vary - eg xx1 etc...

**DonkeyOte** · 12-29-2010, 11:19 AM

I am heading off line shortly - or at least won't be posting much so figured I'd add something.

Based on your examples whereby alphas are only ever single char I think the "simplest" thing is to create a new key for sorting based on a split of the originals.

If we assume your values listed are in A1:A6 then

B1:
=LEFT($A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},$A1&1234567890))-1)
copied down

C1:
=LOOKUP(9.99E+307,LEFT(1&REPLACE($A1,1,LEN($B1),""),ROW($1:$15))+0)
copied down
(add the 1 to cater for possibility of leading zeroes of first # in string)

D1:
=LEFT(REPLACE($A1,1,LEN($C1),""),MIN(FIND({1,2,3,4,5,6,7,8,9,0},REPLACE($A1,1,LEN($C1),"")&1234567890))-1)
copied down

E1:
=(0&REPLACE(A1,1,LEN(C1&D1),""))+0
copied down

we can then combine B:E into a new key which we can use as sort key

F1:
=B1&TEXT(REPLACE(C1,1,1,""),"00000000")&IF(D1="","Z",D1)&TEXT(E1,"00000000")

not very elegant I grant you...
in above we have assumed # are always <= 99999999

**poetstorm** · 12-29-2010, 11:27 AM

So far they seem to be single letters. Thanks for your help. I think I’m a bit more of a basic user than this though, where do I enter all of that? Surely it won’t all go into the formula bar? Can you give me a bit more of a step by step? Thanks!