We help IT Professionals succeed at work.

Data won't sort in Microsoft Excel, please help

I have attached an Excel file (and its CSV equivalent) containing just one column and nine rows of data. For some reason, I can't get it to alphabetically sort.

Can anyone give this a shot and tell me if you can sort it alphabetically? And if not, can anyone tell me why?Won-tSort.csvWon-tSort.xlsx

The data inside the file is simply:

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

For some reason, I cannot make this sort properly with Excel.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
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
Paul MacDonaldDirector, Information Systems
CERTIFIED EXPERT

Commented:

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.


Author

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

Author

Commented:
Well, that is not right. How do I make it not ignore the hyphen?
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
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
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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?

Author

Commented:
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.
Paul MacDonaldDirector, Information Systems
CERTIFIED EXPERT

Commented:

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.

Author

Commented:
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.

Author

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

Author

Commented:
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??
Paul MacDonaldDirector, Information Systems
CERTIFIED EXPERT

Commented:

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


Won-tSort.xlsx

Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
Office 365 subscribers on monthly update channel just got the dynamic arrays feature, along with the SORT function.

I took advantage of the SORT function to make hyphens sort after all the letters by putting this formula in a single cell. ZZZZ will sort after every other character in the default sort order described by David McRitchie. So I used SUBSTITUTE to replace hyphens with ZZZZ, then SORT, then replace ZZZZ back to hyphens.
=SUBSTITUTE(SORT(SUBSTITUTE(A2:A10,"-","ZZZZ")),"ZZZZ","-")

Open in new window

Author

Commented:
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.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Open in new window

Author

Commented:
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.
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
@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      —
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

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