sorting hyphenated numbers

[Guest]

I have three different lists of hyphenated numbers I need to sort together.
All three have the same formating. ex. #####-##-#. The left most numbers
can be 1 - 6 numbers. I have put all the lists in one column. Each set of
numbers is a different color to tell them apart. I have formated the column
as numbers. I tried formatting as text but it took out all the hyphens. The
reason for the sort is to find common numbers in each of the lists.

List one and two sort together correctly, but it seems the sort ignores the
last list. There are no blank rows between them. I select all the numbers
to sort.

The first two lists sort in the 1, 2, 3, order as,
53-19-0
56-95-1
58-38-8
62-56-6
64-67-5

the last list sorts as
100-41-4
100-42-5
10043-01-3
102-71-6
111-27-3
25322-68-3
36653-82-4

Either way is alright. -- I sorted six different lists together this way
before and they sorted correctly. I am using Excell 2002 with XP. I would
appreciate any help you can give.

Punkster

 

[Guest]

If the data does indeed have hyphens between the numbers it should already be
treated as text and thus it will sort alphanumerically (as shown by the 3rd
set of numbers you listed)
It puzzles me that you said it eliminates the hyphens if you format to text?
Also when you say it does not add list three to the sort what do you mean?
From your example all of list 3 would end up being at the bottom of the sort.

(You could extract the various parts with LEFT, RIGHT and MID functions and
then change them to sort as numbers if required.)

 

[Guest]

The data does indeed have hyphens in the numbers. I have gone back to the
original lists of numbers, copied them, and pasted them in a new spread
sheet. I have not changed the formatting in anyway. I tried sorting them
and they still have the same problem. Only two sets of numbers sort
together. The third set did not sort with them.
I have noticed today that when looking at a number in the formula bar two of
the sets of numbers have no hyphens showing. The third set does have hyphens
showing in the formula bar. What would cause this? This is probably why
they will not sort toghter.
If you copied these numbers, there is one from each list, and pasted into a
spreadsheet, do they keep their formatting? If they do, you will see what I
am trying to explain.

125351-99-7
127126-02-7
100-21-0

Punkster

 

[Dave Peterson]

If you look at Excel's help for "default Sort order", you'd see this:

Alphanumeric sort When you sort alphanumeric text, Excel sorts left to right,
character by character. For example, if a cell contains the text "A100," Excel
places the cell after a cell that contains the entry "A1" and before a cell that
contains the entry "A11."

<snipped>

Apostrophes (') and hyphens (-) are ignored, with one exception: If two text
strings are the same except for a hyphen, the text with the hyphen is sorted
last.

=========

It looks like your data doesn't contain any dots. Maybe you could insert a
helper column and use:

=substitute(a1,"-",".")
and drag down

Then select your whole range and sort by this helper column.

(When you're done, delete the column--or hide it for later???)