sort numbers which have dashes

[otwelmr]

I need to be able to sort numbers of the following format:1-1,1-2,1-3,2-1,2-2,2-3,etc.
Excel is not happy with this. I first have to format the cell as text just to keep excel from reformatting my numbers as dates, but then it doesn't allow me to sort them correctly. Help me please.

 

[The Todal]

I used the numbers you listed and was able to sort them properly. Are you sure you don't have any spaces in any of the cells?

 

[otwelmr]

no spaces, I have a series of 319 of these from 1-1 to 10-35, all with different quantities, when I try to sort, I get the message to sort all together or separate numbers and text. Regardless of which I choose, normally all together, it seems to jumble everything up.

 

[Vancity]

Since its text, Excel will always sort 10-35 before 1-2. Similarly, if you had 12-20 that would sort inbetween 1-2 and 2-15.

Trying using 2 helper columns with string functions to extract the left and right sides of the value, and sort the 3 columns by cols B and C
ColB: VALUE(MID(A1,1,FIND("-",A1)-1))
ColC: VALUE(MID($A1,FIND("-",$A1)+1,2))

Excel 2003 also has error an error checking option "Text Date with 2 Digit Years" you can disable to remove those ugly green triangles.

 

[otwelmr]

thanks, that worked.

 

[lenze]

Create a "Custom List" (Tools>Options>Custom List). Now you can sort using this list as the sort order.

lenze