How to sort text as numbers
 

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

How to sort text as numbers
 

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

"Kevin" wrote:

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

How to sort text as numbers
 

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(A 1),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.

How to sort text as numbers
 

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(VA LUE(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

--


Kevin Wrote:
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


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=534752