Sorting data

Posted by Brian on February 11, 2002 4:39 AM

I have several collumns of alphanumeric data such as..
A123,A1,A132.....etc etc

My problem is when I do a sort Excel seems to be ignoring the full number and just ordering by the first number.ie A111 comes before A2 or A20 for that matter what I need is a way to sort it correctly so it would go A1-A900 in numeric order.

I cannot add preceding zeros to them as this is not the format I require.
Any assistance would be appreciated.

Posted by Mark W. on February 11, 2002 6:43 AM

That's the way text values sort...

You could parse the number to a separate column
using the formula, =RIGHT(A1,LEN(A1)-1)+0, and
sort on that column. In fact you could use
Copy/Paste Special... Values on these numeric
values and apply the custom number format,
"A"0.