sort alphanumeric
 

I want to sort data that contains numbers and letters. I have tried
formatting the row as text and it still does not work. Everytime I sort it
doesn't list the entires in order but rather the numbers first then the
numbers with letters next. Here is what im trying to sort:

1227
1244
1257
1277
1289
1421
1422
1423
1431
1432
1438
1494
1572
1258-A
1258-B
1258-C
1258-D
1258-F
1258-G
1273-A
1273-B
1273-C
1273-D
1273-E
1273-F
1403-E
1403-G

Can anyone Help!

sort alphanumeric
 

If you input a number and then change the format to text, the change does not
"set in" right away. I copied and pasted your list into excel and sorted to
get the result you got. I then changed the format to text and got the same
result. I then hit F2 and then enter for each cell in the list. At this
point it sorted correctly. On way to avoid this, is to use an ' (apostrophe)
before numbers that should be text. This formats it as text to begin with.

1227
1244
1257
1258-A
1258-B
1258-C
1258-D
1258-F
1258-G
1273-A
1273-B
1273-C
1273-D
1273-E
1273-F
1277
1289
1403-E
1403-G
1421
1422
1423
1431
1432
1438
1494
1572

"William" wrote:

I want to sort data that contains numbers and letters. I have tried
formatting the row as text and it still does not work. Everytime I sort it
doesn't list the entires in order but rather the numbers first then the
numbers with letters next. Here is what im trying to sort:

1227
1244
1257
1277
1289
1421
1422
1423
1431
1432
1438
1494
1572
1258-A
1258-B
1258-C
1258-D
1258-F
1258-G
1273-A
1273-B
1273-C
1273-D
1273-E
1273-F
1403-E
1403-G

Can anyone Help!

sort alphanumeric
 

William,

Try a helper column (a spare column for sorting purposes only, hidden
if required) with something like:

=IF(ISERROR(A1+0),1,0)

and formula copy to the end of your data.
Then sort over helper (ascending) and column A (ascending)



William Wrote:
I want to sort data that contains numbers and letters. I have tried
formatting the row as text and it still does not work. Everytime I
sort it
doesn't list the entires in order but rather the numbers first then
the
numbers with letters next. Here is what im trying to sort:

1227
1244
~~
1494
1572
1258-A
1258-B
~~
1403-E
1403-G

Can anyone Help!


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

sort alphanumeric
 

I have the same problem as William, but your solution does not work on my
data. What other suggestions might you have?

"Sloth" wrote:

If you input a number and then change the format to text, the change does not
"set in" right away. I copied and pasted your list into excel and sorted to
get the result you got. I then changed the format to text and got the same
result. I then hit F2 and then enter for each cell in the list. At this
point it sorted correctly. On way to avoid this, is to use an ' (apostrophe)
before numbers that should be text. This formats it as text to begin with.

1227
1244
1257
1258-A
1258-B
1258-C
1258-D
1258-F
1258-G
1273-A
1273-B
1273-C
1273-D
1273-E
1273-F
1277
1289
1403-E
1403-G
1421
1422
1423
1431
1432
1438
1494
1572

"William" wrote:

I want to sort data that contains numbers and letters. I have tried
formatting the row as text and it still does not work. Everytime I sort it
doesn't list the entires in order but rather the numbers first then the
numbers with letters next. Here is what im trying to sort:

1227
1244
1257
1277
1289
1421
1422
1423
1431
1432
1438
1494
1572
1258-A
1258-B
1258-C
1258-D
1258-F
1258-G
1273-A
1273-B
1273-C
1273-D
1273-E
1273-F
1403-E
1403-G

Can anyone Help!

sort alphanumeric
 

This works for me except for the numbers that are multiple digits. Any
suggestions?
example:
1
1a
2
3
4
10
10a
10b
11
200
200a
200b

"Sloth" wrote:

If you input a number and then change the format to text, the change does not
"set in" right away. I copied and pasted your list into excel and sorted to
get the result you got. I then changed the format to text and got the same
result. I then hit F2 and then enter for each cell in the list. At this
point it sorted correctly. On way to avoid this, is to use an ' (apostrophe)
before numbers that should be text. This formats it as text to begin with.

1227
1244
1257
1258-A
1258-B
1258-C
1258-D
1258-F
1258-G
1273-A
1273-B
1273-C
1273-D
1273-E
1273-F
1277
1289
1403-E
1403-G
1421
1422
1423
1431
1432
1438
1494
1572

"William" wrote:

I want to sort data that contains numbers and letters. I have tried
formatting the row as text and it still does not work. Everytime I sort it
doesn't list the entires in order but rather the numbers first then the
numbers with letters next. Here is what im trying to sort:

1227
1244
1257
1277
1289
1421
1422
1423
1431
1432
1438
1494
1572
1258-A
1258-B
1258-C
1258-D
1258-F
1258-G
1273-A
1273-B
1273-C
1273-D
1273-E
1273-F
1403-E
1403-G

Can anyone Help!