A wildcard is a special character that lets you perform "fuzzy" matching on text in your Excel formulas. For example, this formula:
=COUNTIF(B5:B11,"*combo")
counts all cells in the range B5:B11 that end with the text "combo". And this formula:
=COUNTIF(A1:A100,"???")
Counts all cells in A1:A100 that contain exactly 3 characters.
Available wildcards
Excel has 3 wildcards you can use in your formulas:
- Asterisk (*) - zero or more characters
- Question mark (?) - any one character
- Tilde (~) - escape for literal character (~*) a literal question mark (~?), or a literal tilde (~~).
Note: wildcards only work with text, not numbers.
Example wildcard usage
| Usage | Behavior | Will match |
|---|---|---|
| ? | Any one character | "A", "B", "c", "z", etc. |
| ?? | Any two characters | "AA", "AZ", "zz", etc. |
| ??? | Any three characters | "Jet", "AAA", "ccc", etc. |
| * | Any characters | "apple", "APPLE", "A100", etc. |
| *th | Ends in "th" | "bath", "fourth", etc. |
| c* | Starts with "c" | "Cat", "CAB", "cindy", "candy", etc. |
| ?* | At least one character | "a", "b", "ab", "ABCD", etc. |
| ???-?? | 5 characters with hypen | "ABC-99","100-ZT", etc. |
| *~? | Ends in question mark | "Hello?", "Anybody home?", etc. |
| *xyz* | Contains "xyz" | "code is XYZ", "100-XYZ", "XyZ90", etc. |
Wildcards only work with text. For numeric data, you can use logical operators.
More general information on formula criteria here.
Functions that support wildcards
Not all functions allow wildcards. Here is a list of the most common functions that do:
