With a~b in D1 and ab in D2, =COUNTIF(D1:D2,"a~b") returns 1, and the cell it counts is D1. =MATCH("a~b",D1:D2,0) returns 2, which points at D2. Same pattern string, same two cells, and Excel picks a different cell depending on which function reads the pattern. A spreadsheet engine that wants its cached results to agree with Excel has to reproduce that split, along with two more rules: database functions such as DSUM treat bare text as "begins with", and whole-cell Find has to backtrack into the last *
Reports that run into this rarely mention wildcards. They say a server-generated workbook counts two rows fewer than the same file recalculated on a desktop, or that a part number containing a tilde is found by one formula and ignored by the next. Feeding every pattern through a DOS-style file mask gets the everyday cases right, so the drift only appears once real product codes start to contain ~ or [
One pattern string, four Excel rule sets
Excel never unified its matching rules; each feature brought its own. The criteria functions (COUNTIF, SUMIF, AVERAGEIF and the *IFS family) decide per criterion whether wildcards apply at all. The lookup functions (MATCH with match type 0, XLOOKUP with match_mode 2) always apply them. The database functions (DSUM, DCOUNTA and the rest) inherit the Advanced Filter rule, where a bare word is a prefix. The Find dialog has its own whole-cell and partial modes. HotXLS Delphi Component evaluates each family by its own rule, in both the classic engine and the XLSX engine. The table lists which cells match each pattern against one column holding a~b, ab, AB, abc, abcb, a*b and axb, every function in its default case-insensitive mode
| Pattern | COUNTIF / SUMIF | MATCH(…,0) / XLOOKUP mode 2 | DSUM criterion | Find, whole cell, wildcards on |
|---|---|---|---|---|
ab | ab, AB | ab, AB | ab, AB, abc, abcb | ab, AB |
a*b | a~b, ab, AB, abcb, a*b, axb | same as COUNTIF | every entry, abc included | same as COUNTIF |
a~b | a~b only | ab, AB | ab, AB, abc, abcb | ab, AB |
a~*b | a*b only | a*b only | a*b only | a*b only |
=ab | ab, AB | not applicable | ab, AB | not applicable |
Two rows deserve a second look. In the a~b row, COUNTIF and MATCH land on different cells, and hand-typed codes contain tildes more often than anyone plans for. In the a*b row, abc matches for DSUM and fails for COUNTIF, because the database function quietly appends a * to the criterion. The DSUM entry for a~b follows from that same appended star: once it is there, the criterion is a wildcard pattern, and ~b is just an escaped b
When does COUNTIF switch into wildcard mode?
COUNTIF switches into wildcard mode only when the criterion text contains * or ?, escaped or not. Without either character, the criterion is compared with each cell as a whole string, case-insensitively, and a tilde is an ordinary character, so COUNTIF(A1:A7,"a~b") counts the cell that literally holds a~b. One star flips the meaning. In "a~b*" the tilde now escapes the b, the pattern reads as "ab followed by anything", and the a~b cell drops out of the count. HotXLS applies this gate in COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS and the database functions alike
Inside wildcard mode the escape rule is short. ~ makes the next character literal whatever it is, so ~b means b and ~~ means one tilde; a tilde at the very end of the pattern is dropped, so "a*~" behaves as "a*". Square brackets are never special. A criterion of "[x]" counts cells that hold the three characters [x], and "[a-z]" counts nothing on ordinary data. The quickest way to check all of this against your own data is TXLSXWorkbook.Calculate, which evaluates a formula string against the active sheet and returns a Variant; a fresh workbook makes its first added sheet the active one
uses
System.Variants, lxHandleX;
const
Names: array [1..7] of string = ('a~b', 'ab', 'AB', 'abc', 'abcb', 'a*b', 'axb');
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
i: Integer;
procedure Show(const Formula: string);
begin
Writeln(Formula, ' = ', VarToStr(Book.Calculate(Formula)));
end;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Data'); // first sheet, so it is active
for i := 1 to High(Names) do
begin
Sheet.Cells[i, 1].Value := Names[i];
Sheet.Cells[i, 2].Value := 1 shl (i - 1); // 1, 2, 4 ... so a SUMIF total names its rows
end;
Sheet.Cells[8, 1].Value := 5; // a number; A9 stays blank
Show('=COUNTIF(A1:A7,"a~b")'); // 1 no * or ?: plain text, the cell a~b
Show('=COUNTIF(A1:A7,"a~b*")'); // 4 wildcard mode: ab, AB, abc, abcb
Show('=COUNTIF(A1:A7,"a*b")'); // 6 whole-string wildcard, abc excluded
Show('=SUMIF(A1:A7,"a*b",B1:B7)'); // 119 every row except abc (8)
Show('=COUNTIF(A1:A7,"a~*b")'); // 1 the literal a*b
Show('=COUNTIF(A1:A9,"<>ab")'); // 7 the number 5 and blank A9 count
Show('=COUNTIF(A1:A9,"<>")'); // 8 non-blank cells
finally
Book.Free;
end;
end.
What "<>text" actually counts
A "<>text" criterion counts every cell that is not that text, and in Excel that includes numbers, booleans, error values and blank cells. A bare "<>" asks something else entirely: "not a blank cell". It skips empty cells but counts every value, including the empty text that a formula such as ="" returns. The Delphi trap sits in the Variant comparison. Compare 'ab' with a numeric cell and Delphi tries to convert 'ab' to a number, the conversion raises, and a handler that swallows the exception as "no match" drops numeric cells out of the count. HotXLS counts them, so the last two lines of the example return 7 and 8. The blank-cell side of this, including what an empty operand equals in an ordinary comparison, is covered in how HotXLS handles comparison chains, blank cells and SUMIF
Why does MATCH find ab when you search for a~b?
MATCH with match type 0 and XLOOKUP with match_mode 2 are always in wildcard mode, so the tilde is an escape even when the pattern contains no * or ?. On the two-cell range from the opening, MATCH("a~b",D1:D2,0) returns 2. On a range that holds only a~b, the same call returns #N/A. Looking up the literal text a~b takes "a~~b". When lookup keys come from user input or an external system and might contain a tilde, double every tilde before building the formula; that one StringReplace is cheaper than explaining a missing row later
HotXLS keeps these two decisions apart. MATCH, XLOOKUP and the criteria functions share one backtracking matcher, with the same escape handling and the same trailing-tilde rule; what differs is the gate in front of it. The criteria path first asks whether the text contains * or ?. The lookup path never asks. A single "match a pattern" entry point used by both would get one family right and the other wrong, whichever way the gate was set. Wildcard lookups carry one more condition: XLOOKUP returns #VALUE! when match_mode 2 is combined with a binary search mode, which is covered in the HotXLS guide to XLOOKUP and XMATCH search modes
DSUM criteria: bare text means "begins with"
DSUM and the other database functions read a text criterion without a leading =, < or > as a prefix, with wildcards still active. That is the Advanced Filter rule, and it differs from COUNTIF on purpose. Over a Name column holding abc, ab, xab and AB, the criterion ab matches abc, ab and AB, while =ab matches only ab and AB. <>ab is a whole-entry inequality, a*b and a? are prefix patterns too, and >ab is an ordinary text comparison. xab never matches ab; the rule is "begins with", not "contains"
The awkward part for an implementation is that a criteria parser which strips the operator sees ab and =ab as the same equality test. Only the raw criterion text tells them apart. HotXLS therefore looks at that raw text before trusting the parsed condition: a text criterion whose first character is not =, < or > gets a * appended and goes through the wildcard matcher, and everything else keeps its whole-entry comparison
Building a criteria range in code has one engine-specific catch. In the XLSX engine, assigning the string '=ab' to TXLSXCell.Value stores text, which is exactly what a criteria cell needs. The classic TXLSWorkbook engine compiles a string value that starts with = as a formula unless you prefix it with an apostrophe. For criteria ranges built at runtime, the XLSX engine is the less error-prone choice. The loop below swaps five criteria into D2 and recalculates the same DSUM each time
const
Names: array [1..7] of string = ('a~b', 'ab', 'AB', 'abc', 'abcb', 'a*b', 'axb');
Criteria: array [0..4] of string = ('ab', '=ab', '<>ab', 'a*b', 'a~*');
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
i: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Db');
Sheet.Cells[1, 1].Value := 'Name';
Sheet.Cells[1, 2].Value := 'Val';
for i := 1 to High(Names) do
begin
Sheet.Cells[i + 1, 1].Value := Names[i];
Sheet.Cells[i + 1, 2].Value := 1 shl (i - 1);
end;
Sheet.Cells[1, 4].Value := 'Name'; // criteria header in D1
for i := 0 to High(Criteria) do
begin
Sheet.Cells[2, 4].Value := Criteria[i]; // stays text in the XLSX engine
Writeln(Criteria[i], ' -> ',
VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
end;
// ab -> 30 ab, AB, abc, abcb (begins with)
// =ab -> 6 ab, AB (whole entry)
// <>ab -> 121 everything except ab and AB
// a*b -> 127 a*b* matches all seven, abc included
// a~* -> 32 only the literal a*b
finally
Book.Free;
end;
end;
Ordering criteria such as >ab follow yet another rule. A plain code-point comparison makes "a~b">"ab" TRUE, because ~ sits above every letter in Unicode. Excel puts punctuation before letters and returns FALSE. On Windows, HotXLS evaluates > and < text criteria, ordinary text comparison and sorting with the system's word-sort collation for the current user locale, so all three agree with each other and with Excel
Whole-cell Find in Delphi: backtracking into the last star
The tempting shortcut for whole-cell Find is to reuse the partial matcher behind Replace and then demand that the match cover the entire cell. It fails on ordinary data. A partial matcher returns as soon as the pattern is used up, so a*b against abcb stops after ab, has consumed 2 of 4 characters, and the cell is rejected. HotXLS runs whole-cell Find through a separate matcher that treats "pattern ended, text did not" as one more mismatch and retries from the last star. With that, a*b matches abcb and a?b*b matches axbyb, as Excel Find does with "Match entire cell contents" checked
Excel Find, in both whole-cell and partial mode, treats ~ as an escape for any following character: a~b finds ab, a~~b finds a~b, and a trailing tilde is ignored, so q~ behaves as q. A matcher that only knows ~*, ~? and ~~ would make a~b find the text a~b, the opposite cell. HotXLS escapes any character after a tilde, in Find and Replace as in formulas
In the XLSX engine the call is TXLSXWorksheet.FindText with a TXLSXFindOptions set. lxfUseWildcards turns on *, ? and ~; lxfWholeCell requires the whole cell to match; lxfMatchCase makes the comparison case-sensitive. Without lxfUseWildcards every character, star included, is literal. Find looks at text values only. Numeric cells are skipped, and formula cells are skipped unless lxfSearchFormulas is set, in which case the formula text is searched. lxfBackward and lxfByColumns change the scan order. The anchor given by StartRow and StartCol is inclusive, so a Find All loop has to step one column past each hit
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Row, Col, NextRow, NextCol, Changed: Integer;
Opts: TXLSXFindOptions;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Parts');
Sheet.Cells[1, 1].Value := WideString('abc');
Sheet.Cells[2, 1].Value := WideString('abcb');
Sheet.Cells[3, 1].Value := WideString('a~b');
Sheet.Cells[4, 1].Value := WideString('ab');
Opts := [lxfUseWildcards, lxfWholeCell];
if Sheet.FindText('a*b', Row, Col, Opts, 1, 1) then
Writeln('a*b whole cell -> row ', Row); // 2: abc rejected, abcb backtracks
if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
Writeln('a~b whole cell -> row ', Row); // 4: ~b is an escaped b
if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
Writeln('a~~b whole cell -> row ', Row); // 3: ~~ is one literal tilde
// Partial match, Find All: the anchor cell is included, so step past each hit
NextRow := 1;
NextCol := 1;
while Sheet.FindText('a*b', Row, Col, [lxfUseWildcards], NextRow, NextCol) do
begin
Writeln('a*b contained in row ', Row); // rows 1, 2, 3 and 4
NextRow := Row;
NextCol := Col + 1;
end;
// Whole-cell wildcard replace rewrites the literal a~b only
Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
Writeln(Changed, ' cell(s) replaced'); // 1
finally
Book.Free;
end;
end;
The partial loop reports all four rows, abc included, because in partial mode a*b only has to occur somewhere inside the cell. FindTextIn and ReplaceTextIn take the same options plus a FirstRow, FirstCol, LastRow, LastCol window, the programmatic version of searching within a selection; pass 0 for any bound to leave it open. The classic engine exposes the same matching rules through an overload with three booleans, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), plus a matching ReplaceText overload. Its row and column results are 1-based, and like the XLSX version it skips formula cells and non-text values
var
Classic: IXLSWorkbook;
Sheet: TXLSWorksheet;
Row, Col: Integer;
begin
Classic := TXLSWorkbook.Create;
Sheet := Classic.Sheets.Add;
Sheet.Range['A1', 'A1'].Value := 'abcb';
// MatchCase = False, UseWildcards = True, WholeCell = True
if Sheet.FindText('a*b', Row, Col, False, True, True) then
Writeln('found at ', Row, ',', Col); // 1,1
if not Sheet.FindText('a*c', Row, Col, False, True, True) then
Writeln('a*c does not cover abcb');
end;
Why a DOS file mask is the wrong matcher for Excel criteria
A file-mask matcher such as MatchesMask speaks a different language from Excel wildcards, even though the two overlap on * and ?. That overlap is exactly why it gets picked, and why its mistakes stay hidden until the data gets interesting:
[x]is read as a character set, soCOUNTIF(A1:A10,"[x]")would count cells holdingxinstead of the bracketed text, and"[a-z]"would match any one-letter cell- There is no tilde escape, so
"a~*b"cannot match a literal asterisk - A malformed mask, such as an unclosed bracket, raises an exception that a caller can easily swallow as "no match", turning a typo in a criterion into a wrong total
- On the lookup side, an escape set limited to
~*,~?and~~falls short too:MATCH("a~b",…,0)has to findab, not the literala~b
If your workbooks only ever use * and ? on plain alphanumeric data, every one of these matchers agrees and none of this matters. Brackets, tildes, mixed-type columns under "<>text" and DSUM criteria written as bare words are where a simpler matcher's totals drift from Excel's, and those are the cases the code above exercises. Saved filters raise a related question about how Excel stores a criterion versus how it compares it, discussed in the HotXLS article on BIFF8 AutoFilter DOPER criteria
HotXLS evaluates COUNTIF, MATCH, XLOOKUP, DSUM and the rest of its function library natively in Delphi and C++Builder, in the classic engine and the XLSX engine, with no Excel installation on the machine. Editions, documentation and the trial build are listed on the HotXLS Delphi spreadsheet component page