Strange behavior of the alphabetic sort order

  • Thread starter Thread starter sylvain levasseur
  • Start date Start date
S

sylvain levasseur

An article in the KB (#168234 XL97: Hyphens and
Apostrophes Are Sorted After Alphabetic Letters
) explains very well how the sort should work in the last
versions of Excel.

But it sounds like if Excel's sorting behaves differently,
which get me in trubbles:

Here are 3 lists A, B and C all sorted by Excel (every
elements are separated by a comma for reading purpose).The
third list does not work as expected since the hyphen
character is not ignored at all. Does anybody have an
explanation ?

List A:1,-,+,a
List B:11,1-,1+,1a
List C:111,1+1,1-1,1a1
 
The Excel 2003 help file adds a little to the explanation:

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

List C sorts as it does, I think, because

111 - is a number (numbers before text)
1+1 - Plus(+) comes before "1" in ASCII order
1-1 - Is the same as the text "11" since hyphen is ignored
1a1 - "a" comes after "1" in ASCII order
 
But adding 11 (eleven) to the list put it first and not close to 1-1. So ?

No it doesn't if it is text, only if it is a number. Remember, the rule is
Numbers first, then text. Format a cell as text first and then put "11" in
it. It will sort just before "1-1" as expected.
 
I'd like to believe you but it does not work as described
with Excel 2002 (10.2614.2625) french version.
 
Back
Top