Technical Article

Excel Wildcards in Delphi: COUNTIF, MATCH, DSUM and Find

Put a~b in D1 and ab in D2, and =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, yet 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 further 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, with every function in its default case-insensitive mode

PatternCOUNTIF / SUMIFMATCH(…,0) / XLOOKUP mode 2DSUM criterionFind, whole cell, wildcards on
abab, ABab, ABab, AB, abc, abcbab, AB
a*ba~b, ab, AB, abcb, a*b, axbsame as COUNTIFevery entry, abc includedsame as COUNTIF
a~ba~b onlyab, ABab, AB, abc, abcbab, AB
a~*ba*b onlya*b onlya*b onlya*b only
=abab, ABnot applicableab, ABnot applicable

Two rows deserve a closer 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

HotXLS wildcard gate diagram: COUNTIF and SUMIF apply wildcards only when the criterion contains a star or question mark, so a~b counts the literal cell and returns 1, whilst MATCH type 0 and XLOOKUP mode 2 are always in wildcard mode, so a~b finds ab at position 2
The gate is the whole difference: COUNTIF asks for a star or question mark before treating a tilde as an escape, MATCH never asks, so one pattern string counts one cell and finds the other

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, whilst =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 inspects 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

HotXLS diagram of the DSUM criterion rule: a bare text criterion gets a star appended and matches as a prefix so ab reaches ab, AB, abc and abcb, equals ab compares the whole entry, angle bracket ab excludes both, and a tilde star survives as the literal a*b, with the resulting DSUM totals 30, 6, 121 and 32
Excel inherited the Advanced Filter rule for database functions: bare text means begins with, whilst a leading equals or not-equals sign compares the whole entry; HotXLS inspects the raw criterion text before trusting the parsed condition

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" ticked

HotXLS diagram of whole-cell wildcard Find backtracking: the pattern a*b consumes a and b in the cell abcb and a matcher that stops when the pattern is used up rejects the cell, whilst the HotXLS matcher treats pattern ended with text remaining as one more mismatch and retries from the last star until the whole cell matches
A whole-cell match is not finished when the pattern runs out; treating leftover text as one more mismatch sends the matcher back to the last star, which is how a*b reaches abcb like Excel Find

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, so COUNTIF(A1:A10,"[x]") would count cells holding x instead 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 find ab, not the literal a~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 required on the machine. Editions, documentation and the trial build are listed on the HotXLS Delphi spreadsheet component page