Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Alphanumeric Sort
I have a range of unique identifiers A1 through A500.
I sort on a different column then need to resort on those unique identifiers. How do I prevent a1 a10 a100 a101 a102 a103 a104 a105 a106 a107 a108 a109 When it should be a1 a2 a3 a4 a5 a6 a7 a8 a9 Thanks, Arturo |
#2
|
|||
|
|||
Because the entries start with "a", Excel interprets the entire cell
and each of the cell's components as text. Even though the cells contain numbers, Excel sorts them textually, and "103" appears before "2" in an alpha sorted list. If the format of your entries is alpha-#-#-# then one way to sort the list is to write this formula for each entry in column B: =Mid(A1,1,1) .... and this entry in column C: =value(mid(A1,2,len(A1)) Then sort simultaneously on columns B and C. |
#3
|
|||
|
|||
And, for completeness, the other way is to use leading zeroes in the numeric
portion of the data in column a, i.e. A003 rather than A3 On 2 Mar 2005 09:33:50 -0800, "Dave O" wrote: Because the entries start with "a", Excel interprets the entire cell and each of the cell's components as text. Even though the cells contain numbers, Excel sorts them textually, and "103" appears before "2" in an alpha sorted list. If the format of your entries is alpha-#-#-# then one way to sort the list is to write this formula for each entry in column B: =Mid(A1,1,1) ... and this entry in column C: =value(mid(A1,2,len(A1)) Then sort simultaneously on columns B and C. |
#4
|
|||
|
|||
Arturo
Easiest would be to have another column with the numbers 1 - 500 Sort on that column to get original order back. Gord Dibben Excel MVP On Wed, 2 Mar 2005 09:03:03 -0800, Arturo wrote: I have a range of unique identifiers A1 through A500. I sort on a different column then need to resort on those unique identifiers. How do I prevent a1 a10 a100 a101 a102 a103 a104 a105 a106 a107 a108 a109 When it should be a1 a2 a3 a4 a5 a6 a7 a8 a9 Thanks, Arturo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort by row instead of by column? | Excel Worksheet Functions | |||
Alphanumeric Sort | Excel Discussion (Misc queries) | |||
"-" ignored in sort | Excel Discussion (Misc queries) | |||
Excel Sort function should not sort the cell formatting! | Excel Worksheet Functions | |||
Data > Sort function amnesia? | Excel Discussion (Misc queries) |