Data won't sort in Microsoft Excel, please help

Highlight Sheet > Sort&Filter >  Custom Sort - My Data has Headers
Sort by >  Inventory ID > Values A to Z > OK



Inventory ID
JBL-CWT128-WH
JBL-CWT128-WRC
JBL-CWT128-WRX
JBLD-124-67001-00X
JBLD-124-67001-01X
JBLD-129-20010-00
JBL-D16R2445
JBL-D16R2450
JBLD-2241H

FWIW, Excel sees these as
JBLC...
JBLC...

JBLC...

JBLD...

JBLD...

JBLD...

JBLD...

JBLD...

JBLD...

...completely ignoring the hyphens.  In its own way, Excel has put them in alphabetical order.

MASQ, it isn't sorted properly. Did you notice?

Well, that is not right. How do I make it not ignore the hyphen?

You don't need to, if you retype the entries you'll find they sort correctly, suspect that hyphen isn't always the same character

The Sheet2 data are sorting either ascending or descending order as though the hyphens aren't there. According to David McRitchie's "rules for sorting" this behavior is the way Excel sorting has always worked.

Do you have a strong reason for needing to sort differently than the way Excel wants to?

Yes, the hyphen is the same. I went as far as printing out the ascii values of each character to make sure there were no hidden control characters.

I did retype them all manually (out of desperation), it still didn't sort properly.

If you create a second column and apply this formula...

   =SUBSTITUTE(A2,"-"," ")

...you can sort on that second column and the order will be what I believe you're expecting.  A2 is the matching cell, of course, and you'll need to copy/paste that formula to match all the affected cells.  

If you need me to, I can re-upload your spreadsheet with the solution.

MASQ,

"The Sheet2 data are sorting either ascending or descending order as though the hyphens aren't there. According to David McRitchie's "rules for sorting" this behavior is the way Excel sorting has always worked.

Do you have a strong reason for needing to sort differently than the way Excel wants to?"

Umm... in this case, isn't it obvious? I want all the JBL- sorted together, and all the JBLD- sorted in its own group. My dataset actually contains 800+ JBL- entries, and almost a hundred JBLD- entries. I can't have it getting mixed up like the way it's happening.

It is incredible to me that this is the way Excel really does it's sorting, are you serious? Excel is totally disregarding ascii characters on its own? Does it have a reason to disregard hyphens??? I feel like I'm hearing something out of this world.

Oh sorry, MASQ, I think I sent you a response that was meant for byundt.

I feel surreal. Since when it is okay to disregard a hyphen during sorting? And from Microsoft, of all companies? I feel like I'm living in a different reality.

I have been sorting data for decades programmatically. You *never* ignore anything and always respect ASCII values. And somehow, I am hearing for the first time (and you all seem to be unanimous) that Microsoft candidly chose to ignore the hyphen and does not provide a flag to make the sort NOT ignore the hyphen. Am I understanding this correctly??

Can't say if the solution will work for you, but it sorts the way I think you want it to.

Won-tSort.xlsx

The person who brought this to attention is actually a layman who doesn't know how to use the string handling formulas. I can do all kinds of string/substring substitutions to make this work (like what byundt was suggesting), but I can't expect a layman to have to go through all that rigmarole just to sort a simple list.

I normally admire and respect Microsoft a great deal, but this is an extremely stupid decision they made to just ignore hyphens without providing a sort mode that sorts by ASCII values. Really, really stupid.

I gave byunt more points because he did go an extra step to provide a solution.

Thank you all for your help.

Here's a macro you can use that does something like what byundt did.

Sub SortWithHyphens()

    ActiveSheet.Columns("A").Replace What:="-", Replacement:="|"
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:M463")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Columns("A").Replace What:="|", Replacement:="-"

End Sub

Select allOpen in new window

Thanks for your response, Martin. Unfortunately I already closed this ticket.

The person I'm trying to help doesn't even know how to use formulas, much less VBA scripts.

I thank you though for responding.

@Martin, just trying with various other characters, what I am seeing is that | (ASCII Char 5) doesn't sort either.

See below for what I am seeing, the numbers are the range of ASCII codes and whether they sort:

1      to      9      No      
10      to      13      Yes      
14      to      31      No      
32      to      38      Yes      
39      to            No      '
40      to      44      Yes      
45      to                  -
46      to      47      Yes      
48      to      57      Yes      Numbers
58      to      64      Yes      
65      to      90      Yes      Upper Case
91      to      96      Yes      
97      to      122      Yes      Lower Case
                        
150      to            No      –
151      to            No      —

elepil, it looks like I forgot to attach this. Just have your user click the blue button.
29172948.xlsm