Sorting Alpha Numeric Part 2

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

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

"CS Project Man" wrote:

> 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
>

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


CS Project Man wrote:
> 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
>

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

"CS Project Man" wrote:

> 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
>