Menu
I'm getting an unexpected result when I sort a list with Excel or with some VBA (Range.Sort). It seems that the hyphen is simply ignored when the text is sorted:
List sorted with Excel
align-content
align-items
alignment-baseline << Should be after "align-self"
align-self
animation-delay
The same list sorted with Libre Office
align-content
align-items
align-self
alignment-baseline
animation-delay
The documentation doesn't provide any information on this behavior:
https://support.office.com/en-gb/article/Sort-data-in-a-range-or-table-62d0b95d-2a90-4610-a6ae-2e545c4a4654?ui=en-US&rs=en-GB&ad=GB#__toc246836970
https://msdn.microsoft.com/en-us/library/office/ff840646.aspx
So is there any rule behind this behavior ?
Is there an option to sort the list the same way a human would do (as Libre Office does) ?
936
Sorting alpha numeric strings Any alpha numeric sort places the alpha in order, and then numbers in order by their first numeral, so 1300 will come before 140 which does not work well for lists like call numbers in libraries.
Sort by cell color, font color, or icon Select a cell in the column you want to sort. On the Data tab, in the Sort & Filter group, click Sort. In the Sort dialog box, under Column, in the Sort by box, select the column that you want to sort. Under Sort On, select Cell Color, Font Color, or Cell Icon.
The reason this happens is because Excel has decided that the 'numbers' are actually text and so it is sorting the 'text'. So in much the same way that words sort based on there letters, the numbers sort on the digits instead of the value.
Excel ignores hyphens in doing the sort, unless that is the only difference between two words.
One work around is to create a "helper column", replacing the hyphen (code 45) with a dot (code 46). Sort on the helper column
=SUBSTITUTE(A1,"-",".")
I have read someplace that the default sort order has a sequence:
with the first two generally ignored
That being the case, replacing the hyphen with a space might be more logical.
152
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
