Technical Article

HotXLS Text Comparison: Excel Word Sort Order in Delphi

In Excel, ="a-b">"ab" returns TRUE, and ="a~b"<"ab" returns TRUE too. Hand the same pairs to Delphi's CompareText and both answers flip, because the hyphen sits below the letters in code-point order and the tilde sits above them. Excel compares text with the Windows user locale's case-insensitive "word sort", where hyphens and apostrophes only break ties and every other punctuation mark sorts ahead of digits and letters

The symptoms rarely mention collation. A COUNTIF(A:A,">M") computed on the server counts two more rows than the same formula in Excel. A price list sorted by a reporting service puts X-100 somewhere Excel would not. VLOOKUP("ABC",...) returns #N/A although the column plainly holds abc. All three reduce to one question: when both operands are text, which one is smaller? Excel has a precise answer, and it is rarely the one Delphi code gives by default

What rule does Excel use to compare two text strings?

Excel compares two text values with the word sort of the current Windows user locale, ignoring case. Word sort is the default collation of the Windows NLS comparison functions: letters compare in linguistic order rather than by code point, so é lands between e and f, and upper and lower case fold together. Two characters get special treatment. The hyphen - and the apostrophe ' are skipped on the first pass, which puts co-op right next to coop; only when the remaining characters tie does their presence, and their position, decide the order. Every other punctuation mark counts at full weight and sorts before the digits, and the digits sort before the letters

The table sets Excel's verdict for IF(A<B,...) against the two comparisons a Delphi developer is most likely to write first

A vs BExcel / HotXLSCompareStr (ordinal)CompareText
"a-b" vs "ab"greaterlessless
"a'b" vs "ab"greaterlessless
"a~b" vs "ab"lessgreatergreater
"a_b" vs "ab"lesslessgreater
"ab" vs "AB"equalgreaterequal
"é" vs "f"lessgreatergreater
"Z" vs "f"greaterlessgreater

Two consequences catch people out. Because the hyphen only breaks ties, ="a-b"="ab" is FALSE: the two strings are immediate neighbours in a sort and still unequal. And because case folds away entirely, ab, AB and Ab are one key for every comparison operator. Under an English user locale, a column of 20 awkward words sorts into a b, a.b, a_b, a~b, a0, a1b, ab / AB / Ab, ab-, a'b, a-b, -ab, ab1, abc, b, e, é, f, Z. Inside the ab group the position of the skipped character decides: trailing hyphen first, leading hyphen last

HotXLS word sort diagram ranking all 20 test words from a b, a.b, a_b and a~b through a0 and a1b, then the ab group with AB and Ab, hyphen and apostrophe variants like a-b and a'b, up to abc, b, e, e-acute, f and Z, showing punctuation before digits before letters with case ignored
Punctuation and space sort before digits and digits before letters, case folds away, and the hyphen with the apostrophe only break ties; that is why a-b lands beside ab yet still compares greater

Checking the collation against Excel yourself

Excel's documentation never names the collation it uses, so the reliable way to confirm it on your own machine is to let Excel judge a batch of string pairs and compare its verdicts with CompareStringW under different flag sets. Put pairs in two columns, have Excel evaluate =IF(A1<B1,-1,IF(A1=B1,0,1)) for each row, and run the same pairs through the API. The pairs worth generating mix ASCII punctuation, digits, both letter cases, spaces, accented letters such as é, ß and ä, CJK characters, full-width forms and the non-breaking space, with plenty of near-misses that differ by a single character. Random words from a dictionary are a poor sample, since plain letters sort identically under every rule in this article

The flag sets lead to clearly different results:

  • NORM_IGNORECASE alone, with LOCALE_USER_DEFAULT, is the plain word sort and the one Excel follows
  • Adding SORT_STRINGSORT switches to string sort, where the hyphen and apostrophe become ordinary symbols with full weight, so every a-b / ab style pair comes out the other way round
  • Adding NORM_IGNOREWIDTH makes a full-width letter equal to its half-width form, and Excel keeps those two apart

One trap sits in the test harness itself. Excel treats a leading apostrophe typed into a cell as the "store as text" prefix, so a cell whose whole input is ' ends up empty, and any pair built that way reports a mismatch that has nothing to do with collation. Build such pairs with string literals in the formula (="'"), or skip them

Why do CompareText and ordinal comparison get it wrong?

CompareText and ordinal comparison miss Excel's order because they compare UTF-16 code units, and code-point order scatters punctuation around the alphabet. The hyphen is U+002D and the apostrophe U+0027, both below every letter, so an ordinal comparison calls "a-b" smaller than "ab" where Excel treats the hyphen as a tie-breaker. The tilde is U+007E, above every letter, which makes "a~b" larger, the opposite of Excel. CompareText adds a second distortion of its own: it folds only a..z to upper case and then compares code units, and since the underscore (U+005F) lies between the upper-case and lower-case ranges, folding moves "a_b" from below "ab" to above it. Neither function has any idea that é belongs between e and f

HotXLS comparison diagram contrasting code point order with Excel word sort: ordinal comparison puts the apostrophe, hyphen and underscore at 0x27, 0x2D and 0x5F around the letters so a-b versus ab comes out less, while word sort pushes punctuation before digits and letters and treats only the hyphen and apostrophe as tie breakers
Code points scatter punctuation around the letters, so ordinal and ASCII-folding comparisons flip the verdicts; word sort moves punctuation in front of the digits and demotes the hyphen and apostrophe to tie breakers

The usual Delphi tools fall on both sides of the line:

  • CompareStr, the string < operator and TComparer<string>.Default are ordinal and case-sensitive, so TArray.Sort<string> without a comparer puts Z before f
  • CompareText and SameText are ordinal after ASCII-only case folding
  • AnsiCompareText and WideCompareText in the Delphi RTL on Windows call CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), the call that matches Excel. A sorted TStringList with its defaults (UseLocale True, CaseSensitive False) goes through AnsiCompareText and agrees with Excel too
  • On POSIX targets the Delphi RTL routes AnsiCompareText through an ICU collator, a different algorithm with different punctuation rules. Free Pascal's AnsiCompareText on Windows converts to the ANSI code page and calls CompareStringA, which loses every character that code page cannot represent

So the locale-aware RTL functions happen to be right on Windows because of how they are implemented today. Code that must agree with Excel should make the API call explicitly and say so. The harder problem shows up in any formula engine where each code path grabs whichever comparison is nearest: upper-case and compare code points in the = and < operators, a case-sensitive Variant comparison in the > / < branch of COUNTIF criteria, another one inside VLOOKUP (which is exactly how VLOOKUP("ABC",A1:A20,1,FALSE) fails to find abc), and a locale call in the range sort. Each path is defensible on its own. Together they produce three different orders over the same column

One comparison behind formulas, lookups and sorting in HotXLS

HotXLS sends every text-versus-text comparison in its calculation and sorting code through a single function, XlsCompareText in lxStandard.pas, which calls CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) and subtracts CSTR_EQUAL. The six comparison operators use it, as do element-wise comparisons in array formulas, the >, <, >= and <= branches of COUNTIF-style criteria and the database functions, VLOOKUP and HLOOKUP in exact and approximate mode, the ordering behind the dynamic-array functions and XLOOKUP / XMATCH, and SortRange on both TXLSWorksheet and TXLSXWorksheet. Putting the range sort on the same function was the deciding design point. Approximate VLOOKUP on text only gives sensible answers when the column was sorted in the order the lookup compares in, and sharing one function makes it impossible for those two orders to drift apart

HotXLS routing diagram showing every text comparison path, from the six comparison operators and COUNTIF style criteria through VLOOKUP, HLOOKUP, XLOOKUP and the range sort of both engines, converging on XlsCompareText, which calls CompareStringW with LOCALE_USER_DEFAULT and NORM_IGNORECASE and maps 1, 2, 3 to -1, 0, 1
Operators, criteria, lookups and sorting share one function, so the order Excel sees and the order HotXLS sorts with cannot drift apart; the API returns 1, 2 or 3, and zero means failure, not less than

The quickest way to see the rule from Delphi is TXLSXWorkbook.Calculate, which evaluates a formula string against the active sheet and returns the result as a Variant

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate evaluates against the active sheet
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: hyphen only breaks ties
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: tie broken, not equal
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: punctuation first
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: case ignored
  finally
    Book.Free;
  end;
end;

Word sort only applies once both operands are text. Mixed types follow a fixed ranking where every number is below every text value and every text value is below every boolean, as described in the article on comparison chains, blank operands and SUMIF. Wildcard criteria such as "a*" or "=ab" are pattern and equality tests with rules of their own, covered in the guide to Excel wildcards in COUNTIF, MATCH and DSUM; the collation discussed here decides the ordering operators

The next example loads the 20 words into column A of a sheet, checks three criteria counts and a lookup, then sorts the column with TXLSXWorksheet.SortRange. The expected counts follow directly from the sorted order shown earlier: eleven words sort after the ab group, eleven sort before a-b, and the three members of the ab group join the eleven for >=AB

const
  Words: array [0..19] of string = ('ab', 'a-b', 'a~b', 'a_b', 'AB', 'a b',
    'ab1', 'ab-', '-ab', 'abc', 'a''b', 'Ab', 'b', 'a.b', 'a1b', 'a0',
    #$00E9, 'e', 'f', 'Z');
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  i: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Words');
    for i := 0 to High(Words) do
      Sheet.Cells[i + 1, 1].Value := WideString(Words[i]);

    // Expected from the word-sort order: 11, 11, 14
    Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,">ab")')));
    Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,"<a-b")')));
    Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,">=AB")')));

    // Finds abc: the lookup compares case-insensitively, as Excel does
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // One key column, ascending: a b, a.b, a_b, a~b, a0, a1b, ab, AB, Ab, ...
    Sheet.SortRange(1, 1, 20, 1, [1], [False]);
    for i := 1 to 20 do
      Writeln(VarToStr(Sheet.Cells[i, 1].Value));
  finally
    Book.Free;
  end;
end;

SortRange runs a stable merge sort, so ab, AB and Ab, which compare equal, keep the relative order they had before the sort. Blank cells go to the bottom whether the key is ascending or descending, which is where Excel puts them

How do I match Excel's sort order in my own Delphi code?

To reproduce Excel's text order outside a workbook, call CompareStringW with LOCALE_USER_DEFAULT and NORM_IGNORECASE, and leave out SORT_STRINGSORT and NORM_IGNOREWIDTH. Watch the return value. The API does not return a signed result; it returns CSTR_LESS_THAN (1), CSTR_EQUAL (2) or CSTR_GREATER_THAN (3), and 0 when the call fails. Subtracting 2 gives the usual negative / zero / positive convention, but only after the 0 case has been handled, otherwise a failed call turns into -2 and reads as "less than"

uses
  Winapi.Windows, System.SysUtils, System.Generics.Defaults,
  System.Generics.Collections;

// Excel's text order: user-locale word sort, case-insensitive
function ExcelCompareText(const A, B: string): Integer;
var
  R: Integer;
begin
  R := CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE,
    PWideChar(A), Length(A), PWideChar(B), Length(B));
  if R = 0 then
    RaiseLastOSError;          // 0 is a failure, not a comparison result
  Result := R - CSTR_EQUAL;    // 1/2/3 become -1/0/1
end;

var
  Keys: TArray<string>;
begin
  Keys := ['abc', 'a-b', 'AB', 'a~b', '-ab', 'ab'];
  TArray.Sort<string>(Keys, TComparer<string>.Construct(
    function(const L, R: string): Integer
    begin
      Result := ExcelCompareText(L, R);
    end));
  // a~b, ab / AB (equal, either order), a-b, -ab, abc
end;

If HotXLS is already in the uses clause, lxStandard.XlsCompareText is public and returns the same ordering. The two differ only on a failed API call: the function above raises, while XlsCompareText falls back to comparing the upper-cased strings by code unit so that a recalculation is never interrupted halfway. For a standalone sort, raising is the better choice, because a silently wrong order is harder to track down than an exception

TArray.Sort is not stable, so equal keys such as ab and AB can come out in either order. When the original order of equal keys matters, sort an index array and use the original position as a secondary key

Sometimes a column must deliberately ignore Excel's order. Part numbers are the classic case: X-100 and X100 are different codes and should sort by code point. SortRange has an overload that takes a TXLSSortCompareEvent, declared as function(const Left, Right: Variant): Integer of object, and uses it in place of the built-in comparison

uses
  System.SysUtils, System.Variants, lxStandard, lxHandleX;

type
  TPartNumberOrder = class
    function Compare(const Left, Right: Variant): Integer;
  end;

function TPartNumberOrder.Compare(const Left, Right: Variant): Integer;
begin
  // A custom comparer also receives empty cells (as Null): place them yourself
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinal, case-sensitive
end;

var
  Sheet: TXLSXWorksheet;   // a filled sheet, rows 2..501, columns A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // keyed on column A, ascending
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

With a custom comparer, SortRange hands over the raw key values and leaves blank placement to you, which is why the comparer above deals with Null first. For a descending key the sort negates whatever the comparer returns, so in that direction blanks move to the top unless the comparer accounts for it. A column sorted this way is no longer in the order that approximate VLOOKUP or a binary-search XLOOKUP expects; what goes wrong in those modes on data sorted in a different order is covered in the guide to XLOOKUP and XMATCH binary search modes

Why can the same workbook sort differently on another machine?

A workbook can sort differently on another machine because Excel's text order depends on the Windows user locale, and HotXLS follows that dependency on purpose. Word sort is language-specific. The Swedish collation, for instance, places ä after z, while English and German keep it next to a, so a file recalculated by a colleague in Stockholm can return a different COUNTIF(...,">y") than the same file on a desktop in Chicago. HotXLS passes LOCALE_USER_DEFAULT so that its answers equal Excel's on the same machine. Pinning any fixed locale would look tidier and would make HotXLS disagree with Excel on every machine configured differently

For server-side generation that has three practical consequences:

  • The locale that counts belongs to the account the process runs under. A Windows service or an IIS application pool can have different regional settings from the developer's desktop, so results seen in the IDE are not automatically what production computes
  • Cached formula results written into the file reflect the generating machine's locale. Excel recalculates with its own locale, so a value can change when the file is opened and recalculated elsewhere; Excel behaves the same way with its own files
  • Locales mostly disagree on accented letters, on letter pairs that some languages treat as a single letter, and on non-Latin scripts. Test data made of plain English words will never expose the difference, so put a few names like Ångström and Öberg in the sample

The Excel-matching path is the Windows one: every Win32 and Win64 build, whether compiled with Delphi, C++Builder, or Lazarus and Free Pascal, calls the same CompareStringW. When a build targets a non-Windows platform, XlsCompareText uses the RTL's WideCompareText, whose ordering comes from that platform's own collation library

Ordinary words sort the same under every rule, which is why a wrong collation tends to survive testing until hyphenated part codes, punctuation or accented customer names arrive. HotXLS gives Excel's answer on those inputs in both the XLS and XLSX engines, from the comparison operators through SortRange. Supported Delphi and C++Builder versions, licensing and the trial download are listed on the HotXLS Delphi Excel component page