sorting number with multiple minus signs.....

[Erik van Ast]

We try to sort a list with partnumbers. This list includes
partnumbers starting with a minus sign and including an
extra minus signs (like -12930-03), numbers not starting
with a minussign but including multiple minussigns (like
15-6064-10) and "normal" numbers with numbers or
characters. We want all numbers starting with a minussign
to appear on top. But when we try to sort them, the
partnumber starting with 0... are on top of the list and
the ones with z... are at the bottom. When we format the
cells as number, the partnumbers are sorted with -... on
top and z... at the bottom but the numbers starting with
a "-"sign with multiple "-"signs are not sorted
differently from the numbers not starting with a "-"sign.
In other words, numbers like -1923-03 are sorted in the
same way as 1923-03.
Does anyone know how to handle this problem?

P.S.

We already tried to import the data in Access 2003 and
then sort the list, but it gives the same result.

 

[sheywood]

The part references starting with - sign are obviously special in some
way which is why you want them at the top. Why not try adding another
column which tests for - sign at the beginning of your part references?
You can then use the new column (call it column B) as part of your
sort - eg sort by column B then by column A.

 

[Dave Peterson]

Excel's sort essentially ignores them.

I either split the pieces into separate columns (data|text to columns).
Then sort by those 3 columns.

Or maybe, you can change that dash to something excel won't ignore.

the dash = char(45)
char(44) = , (comma)
char(46) = . (period)

Be careful not to use something that converts your numbers to real numbers!
(maybe an exclamation point??).

Then sort and edit|replace your chosen character with a dash.