View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
tim m tim m is offline
external usenet poster
 
Posts: 430
Default sorting hyphenated numbers

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.)

"punkster" wrote:

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