Sorting alphanumeric values

[rypi]

Hi All,

I have a column that has numbers and alphanumeric values. For instance:

254
18
3133
ABP10
SEP14
897
WN231

I would like to have them sort like this:
18
254
897
3133
ABP10
SEP15
WN231

If I change the cell formats to a number it almost works, however excel is seeing the SEP15 value as a date and converting it to a number. SEP15 gets converted to 39706. Is there anyway to preserve the values and still have it sort as shown?

Thanks in advance.

 

[Richard Schollar]

Hi & Welcome to the Board!

In an adjacent (empty) column use the formula:

=REPT(" ",5-LEN(A1))&A1

copied down as far as you have data. Then Sort your both columns based on this new formula column (ascending and sort as text).

 

[rypi]

Thanks Richard,

Worked like a charm!!

Thanks again.