sorting problems

[tab]

HOT WATER HEATER:50766-1
HOT WATER HEATER:50766-10
HOT WATER HEATER:50766-2
HOT WATER HEATER:50766-3
HOT WATER HEATER:50766-4

This is the data that I am trying to sort by. The item with a -10 should be
at the end but it always comes up this way.

Please help. All of the postings that I looked at have not helped.

Thanks,

 

[baha]

HOT WATER HEATER:50766-1
HOT WATER HEATER:50766-10
HOT WATER HEATER:50766-2
HOT WATER HEATER:50766-3
HOT WATER HEATER:50766-4

This is the data that I am trying to sort by.  The item with a -10 should be
at the end but it always comes up this way.

Please help.  All of the postings that I looked at have not helped.

Thanks,

If you try to add 0 at the beginning of single denomination should be
no problem.like as, istead of 1 need to put 01.
cheers
Baha

 

[EricG]

You might try creating two helper columns that split your entries into two
parts. Let's say your data are in column A, with a header in the first row.

In column B, put in a formula like "=LEFT($A2,SEARCH("-",$A2:$A2,1)-1)".
This will extract all text before the "-" character. Fill down for all of
your entries.

In column C, use the formula "=RIGHT($A2,LEN($A2)-SEARCH("-",$A2:$A2,1))".
This one extracts everything after the "-". Fill down for all of your
entries.

Now select all the data in columns A, B and C. In the sort dialog box
("Data/Sort...") sort the data first by column B and then by column C, both
Ascending. You might get a warning that says that the data in column C are
"numbers formatted as text". Make sure you sort them as numbers!

This should result in the sort order you desire.

HTH,

Eric