Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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:
    <pre>Sub testhyphen()
    If Range("a1").Value > Range("a2").Value Then
    Debug.Print "Greater"
    Else
    Debug.Print "Less Than"
    End If

    End Sub

    </pre>


    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

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    <P ID="edit" class=small>Edited by gwhitfield on 21-Feb-02 20:40.</P>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

    Deb

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

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

    Just to annoy you, and because I was checking the spelling yesterday for a proposal, <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    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

    (Which Forum is this, anyway?)
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    John,

    I was aware of the correct spelling, but in this case, I'm just dealing with data entry by others. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    Ken

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    from XL help :
    <hr>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.<hr>
    As a further annoyance include Wal+Mart and see what way they sort !

    Andrew C

  7. #7
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  8. #8
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    <P ID="edit" class=small>Edited by kjktoo on 25-Feb-02 21:50.</P>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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •