Sorting Alpha Numeric Part 2

[Guest]

Here is some more info, I have items that start with 'A' and 'D' prefixs. 'A'
sorting is not a problem since the numbers only go up to A69. The 'D's
however go to 200. Is there any other way to sort, without retyping
everything below 100, so I don't get the result below? Thanks.

A59
A60
A63
A69
D00
D01
D08
D09
D10
D100
D101
D108
D109
D11
D110
D118
D119
D12
D120
D128
D129
D13
D130
D136

 

[Guest]

Hi,

If your data are in A2:A101, create a helper column B as follows:

In B2, =(CODE(LEFT(A2,1))-64)*10000+MID(A2,2,255)

and autofill to B101. Select both columns (A2:B101) and sort by column B
ascending.

This formula would work as long as the numbers following the first letter do
not have more than four digits (e.g., A9999).

Regards,
B. R. Ramachandran

 

[Roger Govier]

Hi

One way
Assuming your data is in column A, use a helper column with the formula
=LEFT(A1)&TEXT(MID(A1,2,LEN(A1)-1),"000")
Copy down for the length of range of data in column A
Sort based on the helper column.

Alternatively, if you don't mind altering your column A permanently, copy
the whole of the helper column, move your cursor to the first data item in
column A Paste Special>Values.
You can then delete the helper column and sort by column A.


Regards

Roger Govier

 

[Guest]

Thanks for all the suggestions, adding the 00's after the prefix seems the
easy way, I'll just do an Edit Auto fill.