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

Thanks in advance

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

OzGrid Business Applications

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.

Aladin

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

Mine has more parenthesis, so must be better :O)

OzGrid Business Applications

Posted by Eddie on March 01, 2001 6:37 PM

YOU DA MEN!!!!! THANKS, BOTH WORK GREAT!!!