Forum Discussion
Excel - Sorting hierarchical numbers issue
That's not only during the sorting, dash is ignored when two values are compared, i.e. ="10" < "-9" or
="ab" < "-z" both return TRUE.
However, ="99" = "-99" returns FALSE but ="99" = CHAR(173) & "99" returns TRUE.
In general there are 2808 unichars which work the same way as soften hyphen, we may list them as
=LET(
codesRaw, UNICHAR( SEQUENCE(999999,,1 ) ),
codes, FILTER( codesRaw, NOT( ISNA(codesRaw) ) ),
test, codes & "abc" = "abc",
chars, FILTER( codes, test ),
result, HSTACK( chars, UNICODE(chars) ),
result
)
SergeiBaklan The story is actually more complicated. You found 2808 unichars that appear to be completely ignored by comparators but the character "-" is only PARTIALLY ignored. Notice in the following sequence the values with _-173 use CHAR(173) while the _-4 values use a mix of "-" and CHAR(45) and additional numbers and "-" and variations. Notice how all the _-4x are sorted based on the number first and then all of the "-" give slight more value and bump those down (pay particular attention to 4, -4, 4-, -4- variations) but all the _-173 variations are in the same order as were found in the original list:
Yes, things are more complex and the logic behind is not clear, at least for me.
"10" is greater than "$10" as it shall be, at the same time against text comparing logic
"10" is greater than "€10"
"abc" is greater than "£abc"
UNICHAR(79) is greater than UNICHAR(8679)
etc...