How to sort text as numbers

[Guest]

I have the following list of text entries (that appear to be both numbers and
text, but the cells are all formatted as text) that I need to sort in
numerical order from left to right. I can't find a sort option that let's me
do this - can anyone help? Thanks!

3230
5109
6039
51282
1407D
3234H
4108A
4512H

 

[Guest]

Kevin,

In the column next to the one you want sorted. Use this formula: =Left(A1,4)

This will return the first four numbers and this column, simple sort the
columns as normal based on the second column.

Hope this helps,
Teri

 

[Dave O]

This formula works on the assumption that the alphanumeric entries are
all numbers except for the last character.
=IF(ISNUMBER(VALUE(A1)),VALUE(A1),VALUE(MID(TRIM(A1),1,LEN(TRIM(A1))-1)))

This will work for any length field- including the 5 character numeric
field. If you copy this formula into a cell formatted as a number, it
generates numeric results which you can then sort as you desire.

 

[Bryan Hessey]

Assuming that your data is in column A, the item 4108A was meant to sort
between 3230 & 5109, and there are no nore than 7 numeric and 3 alpha
characters, then insert a helper column B and in B1 put

=IF(ISNUMBER(A1),TEXT(A1,"0000000"),IF(ISNUMBER(VALUE(MID(A1,1,LEN(A1)-1))),TEXT(MID(A1,1,LEN(A1)-1),"0000000")&RIGHT(A1,1),IF(ISNUMBER(VALUE(MID(A1,1,LEN(A1)-2))),TEXT(MID(A1,1,LEN(A1)-2),"0000000")&RIGHT(A1,2),IF(ISNUMBER(VALUE(MID(A1,1,LEN(A1)-3))),TEXT(MID(A1,1,LEN(A1)-3),"0000000")&RIGHT(A1,3),A1))))

Formula copy this to the end of your data, and sort on column B. This
will correctly sort your numeric/alpha

You can delete column B afterwards.

Hope this helps

--