Wildcard
A wildcard is a special character that let's you "fuzzy" matching on text in your Excel formulas. For example, this formula:
=COUNTIF(B5:B11,"*combo")
counts alls 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 (~~).
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.
Compatible functions
Not all functions allow wildcards. Here is a list of the most common functions that do:
