Unexpected order for a range sorted with non alpha-numeric characters

Question

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) ?

Answer 1

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:

• apostrophe
• hyphen
• space

with the first two generally ignored

That being the case, replacing the hyphen with a space might be more logical.