# Alpha Numeric Sort Problem (Tough One) For Me

Posted by Eddie on February 28, 2001 10:15 PM

Have tried a few formulas and ideas found in this message board
but, still not finding what works.
I have one column ("A")of alphanumeric part numbers like this
they will have from 1 to 3 numbers before text characters which
are usually 2. They are all run together no spaces or dashes
need to sort by the first 2 alpha characters then by number prefix
then by suffix

23AX543
6QS29305
587KH234
2RT569
893KD345
44GC998

Posted by David Hawley on March 01, 2001 12:02 AM

Hi Eddie

Supposing you text is in Column A, in cell B1 put:
=IF(NOT(ISERR(VALUE(LEFT(A1,3)))),VALUE(LEFT(A1,3)),IF(NOT(ISERR(VALUE(LEFT(A1,2)))),VALUE(LEFT(A1,2)),VALUE(LEFT(A1,1))))

IN C1 PUT:
=MID(A1,LEN(B1)+1,2)

IN D1 PUT:
=VALUE(RIGHT(A1,LEN(A1)-LEN(B1&C1)))

Copy all 3 formulas down as far as needed. Then Copy them and go to Edit>PasteSpecial-Values and paste over the top of themselves. Now you can sort by the order you want.

Any Good?

Dave

Posted by Aladin Akyurek on March 01, 2001 12:13 AM

I think the following procedure should be good enough.

I assume your parts to be in A from A1 on.

In B1 enter: =MID(A1,IF(CODE(MID(A1,1,1))>57,1,IF(CODE(MID(A1,2,1))>57,2,IF(CODE(MID(A1,3,1))>57,3,4))),2)
In C1 enter: =LEFT(A1,FIND(B1,A1)-1)
In D1 enter: =RIGHT(A1,LEN(A1)-FIND(B1,A1)-1)
In E1 enter: =C1&B1&D1

Select the range B1 to E1 and copy down as far as needed.

Select all values in columns A to D, activate the option Data|Sort, choose No header row. Then Sort by B, C, and D.

The column E will contain the parts sorted the way you want.

As you see, this sorting is semi-automated. I don't believe it is worth the effort to make the whole process fully automated.