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 B | Excel / HotXLS | CompareStr (ordinal) | CompareText |
|---|---|---|---|
"a-b" vs "ab" | greater | less | less |
"a'b" vs "ab" | greater | less | less |
"a~b" vs "ab" | less | greater | greater |
"a_b" vs "ab" | less | less | greater |
"ab" vs "AB" | equal | greater | equal |
"é" vs "f" | less | greater | greater |
"Z" vs "f" | greater | less | greater |
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
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_IGNORECASEalone, withLOCALE_USER_DEFAULT, is the plain word sort and the one Excel follows- Adding
SORT_STRINGSORTswitches to string sort, where the hyphen and apostrophe become ordinary symbols with full weight, so everya-b/abstyle pair comes out the other way round - Adding
NORM_IGNOREWIDTHmakes 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
The usual Delphi tools fall on both sides of the line:
CompareStr, the string<operator andTComparer<string>.Defaultare ordinal and case-sensitive, soTArray.Sort<string>without a comparer putsZbeforefCompareTextandSameTextare ordinal after ASCII-only case foldingAnsiCompareTextandWideCompareTextin the Delphi RTL on Windows callCompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), the call that matches Excel. A sortedTStringListwith its defaults (UseLocaleTrue,CaseSensitiveFalse) goes throughAnsiCompareTextand agrees with Excel too- On POSIX targets the Delphi RTL routes
AnsiCompareTextthrough an ICU collator, a different algorithm with different punctuation rules. Free Pascal'sAnsiCompareTexton Windows converts to the ANSI code page and callsCompareStringA, 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
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ömandÖbergin 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