Collating sequence / sort

[Dave Mills]

Can anyone explain to me why my spreadsheet sorts as follows. The cells are
formatted as text. This also happens with other similar data but I just cannot
see why. This does not seem to be either a test or numeric sort. In any
collating sequence I would expect all the T10's to be either before or after T1-
I just cannot see how it can be in the middle.


T10-02
T10-03
T10-04
T10-05
T10-06
T10-07
T10-08
T10-09
T1-01
T10-10
T10-11
T10-12
T10-13
T10-14
T10-15
T10-16
T10-17

 

[Dave Peterson]

Take a look at excel's help for sort and you'll see that excel ignores the
hyphens.

From xl2003's help for "Default sort orders"
.....
Apostrophes (') and hyphens (-) are ignored, with one exception: If two text
strings are the same except for a hyphen, the text with the hyphen is sorted
last.

Maybe you can change the hypen to a different character, do the sort and then
change it back???

 

[Dave Mills]

Thanks Dave. I did not know that and I would not have looked for that
explanation without your prompt. Since this sort is initiated by VBA it will be
quite simple to replace the "-" before the sort and put it back after.