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

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.

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.

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