sort wrong? a-1,a-10,a-11,a-2

[me]

help with sorting right
i have a-1...c-16
i get
a-1,a-10,a-11..a16,a-2...
how do i get my sort to go a-1,a-2,a-3,,,a-10,a-11,a-
12...c-16
thanks me

 

[dlw]

It isn't really sorting wrong- it is sorting it like text,
you want the first bit sorted as text and the last bit
sorted as numbers. You could use =left(a1,2) and =mid
(a1,3,5) to split up the strings, take the value of the
last part, and have two sort keys.
Sorry, I can't explain it very well.

 

[Ron Rosenfeld]

help with sorting right
i have a-1...c-16
i get
a-1,a-10,a-11..a16,a-2...
how do i get my sort to go a-1,a-2,a-3,,,a-10,a-11,a-
12...c-16
thanks me

Add two helper columns.

Assume your data a-1... is in column A.

B1 put the formula =LEFT(A1,1)
C1 put the formula =--TEXT(RIGHT(A1,LEN(A1)-2),"00")

Select the data in all three columns.

Data/Sort and Sort By: Column B (ascending)
then by: Column C (ascending)



--ron