sort alphanumeric

[William]

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!

[Sloth]

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

[Bryan Hessey]

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)

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!

[TZellers]

I've a similar issue, but no matter what I do (format as text, use the text formula, text to columns) I can not get the sort list to work properly. I have numbers from 1 to 100 with some having suffixes such as A, B, etc - even up to AA or AB in some cases. Here a list, which is the closest I can get Excel to sort it the way I want. Please note that 1A follows all numbers beginning with the number 1:

1
100
101
102
14
15
17
18
19
1A
20
23
24
24A
24B
24C
24D
25
26
26A
26B
28
3
30
30A
30K
34
34A
34B
34C
37
37A
39
39A
40
40A
42
43
4A
4B
4C
61
61A
61B
62
62A
62B
62C
67
72
72A
72B
73
75
76
79
91
92
92A
92B
94
94A
94B

My target output would be 1, 1A, 3, 3A, etc. I've even tried copying the list of values to notepad, then re-importing into excel. no matter which path i take, I can not get the list to sort properly. Please help.

thanks,
Troy