- Microsoft Excel
- Microsoft Office
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.
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.
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
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.
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?
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.
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.
"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.
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??
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??
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.
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","-")
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.
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
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.
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 —
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
29172948.xlsm
Premium Content
You need an Expert Office subscription to comment.Start Free Trial