Controlling Sorting Order

It should be noted that the Windows version of Excel does NOT sort text characters in ANSI/ASCII/Unicode order. (Ditto for other Windows apps like Explorer.) The sort order is illustrated in the following table (see Figure 1 below) which is divided into three sections for convenient display. Each section has the following four columns:
1. The code value in ANSI/ASCII/Unicode order
2. The character for that code value: CHAR(col.1)
3. Column 2 sorted by Excel BEFORE dividing into three sections: SORT(col.2)
4. The ANSI code value of the character in column 3: CODE(col.3)
In column 3 notice that Excel sorts apostrophe and hyphen before space, all special characters are before numerals, and upper/lower case letters are together (A a B b C c ...) instead of separate (A B C ... a b c ...) as in ANSI/ASCII/Unicode.
With respect to letters, case sensitive Excel order moves each lower case before its upper case (a A b B c C ...) while case sensitive ANSI/ASCII/Unicode order keeps all upper case before lower case (A B C ... a b c ...).
With respect to VBA:
    Option Compare Text or StrComp(s1, s2, vbTextCompare)
        corresponds to Excel order with case ignored.
    Option Compare Binary or StrComp(s1, s2, vbBinaryCompare)
        corresponds to case sensitive ANSI/ASCII/Unicode order.
Excel's sort order becomes more curious when special characters are combined with letters like X'X or X-X or X:X or X?X or even XxX etc. In this case, the combinations with apostrophe or hyphen are sorted LAST when other things are equal (except perhaps the total number of characters).
My Excel Toolbox includes the SortANSI macro to sort all rows of a selected range by a specified column in ascending or descending order. The text portion of that column will be sorted in ANSI/ASCII/Unicode order, either case ignored or case sensitive, instead of Excel order. Undo (Ctrl+Z) is supported. SortANSI will not sort a range that has formulas because the results can be confusing; be careful when using Excel to sort cells containing formulas.
My Excel Toolbox also includes the following function:
    =SortPlus(RangeArray, [SortIndex], [SortOrder], [LeftRight],
            [HasHeader], [CaseSensitive], [CodeOrder],
            [CustomOrder], [NumberAsText], [TextAsNumber])
The first four arguments match the SORT function available in Excel 2021 or later; the remaining six are added features. All but the first are optional.
RangeArray can be a cell range (contiguous) or an array constant or the result of an array function.
SortIndex is the base-1 index of a column or row to sort by; default is 1.
SortOrder is 1 (default) for ascending or -1 for descending.
LeftRight is FALSE (default) to sort rows or TRUE to sort columns.
HasHeader is FALSE (default) to sort the first row/column or TRUE to skip the first row (if LeftRight is FALSE) or first column (if LeftRight is TRUE).
CaseSensitive is FALSE (default) to sort text with case ignored or TRUE for a case sensitive sort.
CodeOrder is FALSE (default) to sort text in Excel order or TRUE to sort text in ANSI/ASCII/Unicode order; TRUE applies only if CustomOrder and TextAsNumber are both FALSE.
CustomOrder is FALSE (default) to ignore, or a custom list number (like 1 for Sun, Mon, Tue,...), or a temporary custom list as comma separated text (like "N, S, E, W"). Backslash (\) can be used to escape comma in CustomOrder comma separated text.
NumberAsText is FALSE (default) to ignore; if TRUE and RangeArray is a cell range, then numeric cell values displayed as text are considered text.
TextAsNumber is FALSE (default) to sort numeric text separate from numbers or TRUE to treat numeric text as numbers.
The following example returns a case sensitive sort in ANSI/ASCII/Unicode order of a multi-column range with no header row by column 3 descending:
    =SortPlus(D1:H99,3,-1,,,TRUE,TRUE)
The next example sorts an array function result that includes a header first by column 1 then by column 2, both ascending:
    =SortPlus(SortPlus(ListDocProperties(),1,,,TRUE),2,,,TRUE)
SortPlus is slower but more capable than Excel's SORT and does not require Excel 2021+. If used in a cell formula, it returns a dynamic array. When using earlier versions of Excel without support for dynamic arrays, review the PDF file UseSpillArray.pdf.
See https://sites.google.com/view/MyExcelToolbox