What are wildcards in Excel? Definition, characters, and examples

Exceljet's definition adds the useful adjective: a wildcard is "a special character that lets you perform 'fuzzy' matching on text in your Excel formulas." Fuzzy is the point. Real data arrives as "Acme Ltd", "ACME Limited" and "Acme Ltd." in the same column, and a wildcard is the cheapest way to count all three without cleaning the column first.

The three characters

The asterisk is the one people reach for. Exceljet describes it as matching "zero or more characters", which matters at the edges: "Acme*" matches the bare string Acme as well as Acme Ltd, so a trailing asterisk is safe to leave on.

The tilde is the escape hatch, and it exists because product codes contain asterisks and survey answers contain question marks. Ablebits' description is the clearest: a tilde "placed before a wildcard character cancels the effect of a wildcard." So ~* means a literal asterisk, ~? a literal question mark, and ~~ a literal tilde.

Where wildcards work — and where they do not

This is the part that costs an afternoon, because a wildcard passed to the wrong function does not error. It simply looks for a value that literally contains an asterisk, finds none, and returns zero or #N/A.

Ablebits is blunt that "quite a limited number of Excel functions support wildcards", and both Ablebits and Exceljet publish the same list: AVERAGEIF, AVERAGEIFS, COUNTIF, COUNTIFS, SUMIF, SUMIFS, VLOOKUP, HLOOKUP, XLOOKUP, MATCH, XMATCH and SEARCH. That is essentially the conditional-aggregation family plus the lookups; FIND does not appear on it, while SEARCH does.

The second constraint is the type. Exceljet states it in four words — "Wildcards only work with text, not numbers" — and Ablebits explains what that means in practice: in formulas, "Excel fails to recognize a string in a range of numbers", so "*5*" will not find 1500 in a column of numbers. Logical operators and comparisons are the tool there instead. Ablebits notes the rule is narrower than it sounds: Find and Replace and the filter dropdowns handle both text and numbers happily. It is the formula functions that are strict.

Building the criteria

Hard-coding a pattern is straightforward: =COUNTIF(A2:A12, "Acme*") counts everything in the range that starts with Acme. The version that survives contact with users puts the search term in a cell and concatenates it, which is Ablebits' example verbatim:

=COUNTIF(A2:A12, "*"&E1&"*")

Type any fragment into E1 and the count follows it. Wrapping the reference in asterisks on both sides matches anywhere in the string; asterisk only on the right matches the start; only on the left matches the end. The important detail is that the wildcard is part of the criteria string, joined with & — it is not a separate argument, and quoting the whole thing as "*E1*" searches for the literal text E1.

The same pattern is how you get a wildcard into a lookup. Ben Collins' Google Sheets example builds it exactly the same way — =VLOOKUP(G$2&"*",$A$1:$D$51,2,FALSE) for a starts-with match, and =VLOOKUP("*"&G$2&"*",$A$1:$D$51,2,FALSE) when the fragment may sit anywhere in the value. He flags the argument that trips everyone: "It's necessary to use FALSE as the last argument of your VLOOKUP in order for the wildcard matching to work." An approximate-match lookup ignores the wildcard entirely.

Excel and Google Sheets

The three characters and the concatenation pattern behave the same way in both applications, which is why Collins' VLOOKUP formula above would work unaltered in Excel. Where the two diverge is what happens when a pattern outgrows * and ?. Sheets ships a regular-expression family: REGEXMATCH tests "whether a piece of text matches a regular expression", with REGEXEXTRACT and REGEXREPLACE alongside it. A wildcard can only say "something here"; a regular expression can say "three digits followed by a hyphen", which is the rule you actually need for invoice numbers and postcodes.

How Formula Foundry handles this

The failure mode with wildcards is not the concept, it is the punctuation. A criteria string like "*"&E1&"*" is five tokens of quotes, ampersands and asterisks jammed together, and when it is the third argument of a SUMIFS the formula bar shows the whole thing as one unbroken line. Formula Foundry opens the formula in the sidebar across multiple lines with indentation that follows the nesting, and colour-codes it by token type — functions, ranges, constants and named references each get their own colour. A quote closed in the wrong place stops being a needle in a haystack and becomes a run of text in the wrong colour.

The parser backs that up before the formula reaches the cell. Matching parentheses highlight as you type, errors are collected in one pass rather than one at a time, and the messages are specific — a missing operator between two pieces of a concatenation is named as such. It is locale-aware too, so a formula written with ; as the argument separator is understood rather than rejected, and when the separator genuinely is wrong for the sheet the parser says so instead of leaving you guessing.

What it cannot do is tell you whether your pattern matches your data. Formula Foundry has no calculation engine for your workbook — it parses, formats, explains and translates, and never evaluates the sheet. A COUNTIF returning 0 because the asterisk went to a function that does not support wildcards is a fact only the spreadsheet can establish; what the add-on offers there is the AI assistant, which explains a formula step by step in plain English and suggests likely causes when one misbehaves. You can try a criteria string in the free playground in the browser, with nothing installed.

FAQ