Sorting with hyphens in test (XL2K SR-1a)

Topic

New Reply

Something I just found. Can someone replicate or explain what I am doing wrong?

In cell A1, put “Wal-mart”
in cell A2 put “Walmart”

Sort these two cells in ascending order. “Walmart” goes to the top.

In a module, enter the following code:

Sub testhyphen()
If Range("a1").Value > Range("a2").Value Then
    Debug.Print "Greater"
Else
    Debug.Print "Less Than"
End If

End Sub

When I run this code it tell me that “Walmart” is greater than “Wal-mart,” which is the exact opposite of how it sorts.

Is this a bug, or does it have rational explanation, or am I just missing something here? Makes me wonder if there are other sorting/comparison anomalies out there.

Ken

Reply | Quote

Replies

IN the VBE one can set the way comparisons are done with the statement (top of you module)

Option Compare Binary
or
Option Compare Text

With the first, you get a FALSE on the comparison you made, with the second you get your result (TRUE).

Reply | Quote

Edited by kjktoo on 25-Feb-02 21:50.

Edited to change the word “sheets” to “ranges”

Jan Karel,

Once again you hit the nail on the head in your response. The annoyance here is why Microsoft uses a default collating sequence in VBA that is different from the one used when sorting ranges. Just one more thing to annoy the unwary like me I guess. I suppose it has to do with making VBA consistent with VB or something like that. Anyway…

Thanks for your help.

Ken

Reply | Quote

Actually, worksheets are not sorted when you refer to them in ThisWorkbook.Worksheets. They are ordered in the same sequence they are listed in the workbook tabs. So it’s not that VBA does something different in regard to sorting than Excel, it just uses as a more consistent criteria (ASCII sort, not worksheet order). That’s why it’s best to specify the sheet name not just its number because Sheets(3) might not be the same worksheet all the time if you move the sheets around or add/delete them.

Deb

Reply | Quote

Edited by gwhitfield on 21-Feb-02 20:40.

Hyperlinks added

It has to do with how the ASCII table is laid out. First are control characters (like LF, TAB), then punctuation, then numbers, then upper case then lower case. The ‘-‘ appears at position 45 (decimal) and the ‘m’ appears at 109 so 109 > 45 is true.

See http://www.asciitable.com[/url%5D

Deb

Reply | Quote

Just to annoy you, and because I was checking the spelling yesterday for a proposal,
the correct spelling includes the hyphen, and Mart is capitalized:

Wal-Mart Stores, Inc.
NYSE Ticker: WMT

but the website doesn’t use the hyphen:

http://www.walmart.com/cservice/aw_index.gsp%5B/url%5D

(Which Forum is this, anyway?)

Reply | Quote

John,

I was aware of the correct spelling, but in this case, I’m just dealing with data entry by others.

Ken

Reply | Quote

from XL help :[indent]

---

Text and text that includes numbers are sorted in the following order:
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
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.

---

[/indent]As a further annoyance include Wal+Mart and see what way they sort !

Andrew C

Reply | Quote