Excel's Sort Order
 

After much work, I would like to seek advice and comments regarding
Excel's sort order.

I presume that Excel has its own rules (running in the background)
that enable the standard worksheet sort to sort alphabetically & be
case sensitive ... however, VBA code (using Option Compare Binary or
Text, as required) seems to use the ANSI character set, which puts "A"
before "a" when Binary is set. This would therefore imply that other
sort routines that are based on the ANSI set (QuickSort, MergeSort)
cannot sort truly alphabetically (in Excel VBA) .... or more
realistically, as I would like to sort.

For instance, a case sensitive Excel gives the order AAaa, AAaA, AAAa
( a left to right, character by character comparison, "a" before "A"),
whereas a QuickSort with Option Compare Binary gives AAAa , AAaA,
AAaa.

With Option Compare Text, QuickSort says all 3 are equal, and the
eventual sort order seems dependent on the original list/order …. and
looking at how QuickSort works, I'm not surprised!

A possible solution as, I understand it, is to split the data into
sequential alpha-numeric sections and then either :

* Use a worksheet to sort the sections from the right to the left. The
sort is then stable, and advantage is taken of Excel's internal rules
that enable a true alphabetical, case sensitive sort. It can be made
to be remarkably quick, as long as the sections (ie columns) do not
become excessive.

* Pad numbers, rebuild into a single entity and sort on this data. A
stable sort is required to ensure the original data follows …

It is worth noting that Excel also has its own oddities, ie how it
handles the "." and "-" characters … it sure makes life hard when
trying to sort, and complicates the process of splitting into the
alpha-numeric groups.

Even after the splitting/padding/concatenating, I still cannot get a
standard QuickSort to sort "correctly", as it seems to be based on the
ANSI character set, as noted above.

Any comments would be most welcome.

Regards,

Mark