sorting number with "-"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.

 

[David McRitchie]

Hi Erik,
I separated your question into it's own paragraph rather than
keeping it buried. First stop thinking of them as numbers they
are really identifications and should be handled as text.

Microsoft Excel help indicates the following order
(which is not by the ASCII code assignments)
0 1 2 3 4 5 6 7 8 9 ' - (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ `
{ | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

So create a helper column for the sort
=IF(LEFT(A1,1)="-","-"," ")

Internally, digits will appear before hyphen so if you want to change
that you will have to code for them, perhaps something along the lines of
.. http://www.mvps.org/dmcritchie/excel/sorting.htm#ebcdic
but you'll have to make up your own substitutions depending on what
order you want.

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 minus signs (like
15-6064-10) and "normal" numbers with numbers or
characters.

 

[Dave Peterson]

See one more reply to your other post.

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.