How do you change sort defaults for combined text number & hyphen.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to sort an array of part numbers such as (3000, 3000P, 3000-10,
3000-10P, 30000P) and would like them to appear in that order. But I see
that Excel ignores hyphens, which is part of the problem. Is there anyway I
can change the data sorting defaults so the part numbers appear is this order?
 
Another part of the problem is that you want the data to be treated as a
number followed by text. Excel can't do that unless you either split the data
into 2 columns - number part and text part -- or you pad the numeric portion
on the left with spaces to the maximum possible length of the number (i.e. 5
digits in the data you show).

So let's say you use 2 "helper" columns in which you've done that split.
You'll have to put a space in the 2nd column for those entries that consist of
a number only (if it's blank, that row will sort to the bottom).

As for the hyphen problem, you can replace the hyphen with a character that
sorts after Z. One character that might work is character 158, an accented z.
Then you can sort with these 2 columns as the keys.

As far as I can see, this is the only way to get the order you want.

I'm trying to sort an array of part numbers such as (3000, 3000P, 3000-10,
3000-10P, 30000P) and would like them to appear in that order. But I see
that Excel ignores hyphens, which is part of the problem. Is there anyway I
can change the data sorting defaults so the part numbers appear is this
Click to expand...
order?
 
Back
Top