Wildcards in Excel: *, ? and ~ for partial matches
A wildcard is a character that stands for other characters. They let criteria functions match part of a text instead of the whole cell. This guide covers the three wildcards and where they work.
Wildcards only apply to text. They work in COUNTIF, COUNTIFS, SUMIF, SUMIFS, MATCH with match type 0, and XLOOKUP.
The asterisk (*): any number of characters
An asterisk matches any run of characters, including none. "Printer*" matches "Printer toner", "Printer paper" and even "Printer" on its own.
Example: Count tickets that start with Printer
| A | B | C | |
|---|---|---|---|
| 1 | Subject | ||
| 2 | Printer toner | 3 | |
| 3 | Printer paper | ||
| 4 | Scanner | ||
| 5 | Printer cable | ||
| 6 | Monitor |
Three subjects start with Printer. The asterisk lets the criterion ignore what comes after.
The question mark (?): exactly one character
A question mark matches exactly one character. "A?" matches "A1" and "A2" but not "A10", which has two characters after the A.
Example: Count single-digit codes starting with A
| A | B | C | |
|---|---|---|---|
| 1 | Code | ||
| 2 | A1 | 2 | |
| 3 | A2 | ||
| 4 | A10 | ||
| 5 | B1 |
"A1" and "A2" match; "A10" has two characters after A, so it does not match.
The tilde (~): search for a real * or ?
To find an actual asterisk or question mark, put a tilde in front of it. "Sale~*" matches only the text "Sale*", not "Sale" or "50% off".
Example: Count only the literal asterisk
| A | B | C | |
|---|---|---|---|
| 1 | Promotion | ||
| 2 | 50% off | 1 | |
| 3 | Sale* | ||
| 4 | Sale |
Only "Sale*" contains a real asterisk, so the count is 1.
Wildcards with MATCH and XLOOKUP
MATCH accepts wildcards when its match type is 0 (exact). It returns the position of the first match, counted from the top of the range.
Example: Find the first subject that starts with Sc
| A | B | C | |
|---|---|---|---|
| 1 | Subject | ||
| 2 | Printer toner | 3 | |
| 3 | Printer paper | ||
| 4 | Scanner | ||
| 5 | Printer cable | ||
| 6 | Monitor |
"Scanner" is the third item in the list, so MATCH returns 3.
Common mistakes
Using * with a number.** Wildcards match text only; a number such as 1001 is not matched by "100*
Fix: Store codes as text, or compare with a range such as ">=1000" and "<=1999".
Expecting a wildcard to match a space or a dot. Only * and ? are special; spaces and dots match themselves.
Fix: Check the exact text with LEN or by filtering the list.
Using MATCH without the third argument. The default match type is approximate, and wildcards are ignored there.
Fix: Always add 0 as the match type:
=MATCH("Sc*",A2:A6,0).
Frequently asked questions
Are wildcards case-sensitive?
No. "printer*" and "Printer*" find the same cells.
How do I search for text that contains a * or ?
Put a tilde in front of the character, such as "~?" for a question mark. A tilde by itself is written as "~~".
Related functions
- COUNTIFCOUNTIF counts the cells in a range that meet one condition: a text, a number, a comparison like ">100" or a wildcard.
- COUNTIFSCOUNTIFS counts the rows that meet several conditions at once, such as a region and an amount range.
- MATCHMATCH returns the position of a value in a list, for example that "Lena" is the 3rd name.
- XLOOKUPXLOOKUP searches one column or row and returns the matching value from another, in any direction, with a built-in 'not found' message.