Avatar of elepil
elepil
 asked on

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.
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
☠ MASQ ☠

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 MacDonald

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.


elepil

ASKER
MASQ, it isn't sorted properly. Did you notice?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
elepil

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

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
byundt

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?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
elepil

ASKER
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 MacDonald

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.

elepil

ASKER
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
elepil

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

ASKER
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 MacDonald

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


Won-tSort.xlsx

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
byundt

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
elepil

ASKER
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 Liss

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

elepil

ASKER
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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Rob Henson

@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 Liss

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