Wildcard is a term for a special kind of a character that can represent one or more "unknown" characters, and Excel has a wildcard character support. You can use wildcards for filtering, searching, or inside the formulas. In this guide, we're going to show you how to use Excel Wildcard characters for setting up formula criteria.
Excel wildcard characters
Excel supports 3 kinds of wildcard characters:
| Asterisk (*) | Any value of zero or more |
| Question mark (?) | Any single character |
| Tilde (~) |
Escape for an actual question mark, asterisk, or tilde character.
|
You can use these characters to generate a text pattern for strings that are to be matched. Please note that wildcard characters only work with texts, and do not work with numbers.
Let's look at some examples:
| Pattern | Meaning | Sample |
| ? | Any one character | "A", "a", "1", "-", etc. |
| ?? | Any two characters | "A1", "9a", "9.", etc. |
| * | Any characters | "excel", "supp0rtz", "wi!d cards", etc. |
| A* | Starts with "A" | "A", "Anchor", "A string", etc. |
| ?* | At least one character | "Z", "1", "Z1", etc. |
| (???) ???-???? | 10 characters with parenthesis and a hyphen | "(123) 456-7890", "(A10) XYZ-8866", etc. |
| *lec* | Contains "lec" | "electric", dialectic", "lecture", etc. |
| *~? | Ends with a question mark (?) | "O Brother, Where Art Thou?", " Dude, Where's My Car?", etc. |
Formulas that support using wildcard criteria
All …IFS and …IF Functions
All statistical functions in Excel that end with either "IFS" or "IF" support wildcards.
Using wildcard criteria can increase the versatility of these functions. Use strings with wildcards in criteria arguments. The following examples show the difference between using and not using wildcards.
The upper set of formulas are using the "*FIRE*" string which represents any text that contains "FIRE". Thus, the formulas calculates 3 rows of data: "FIRE", "FIRE" and "FIRE, FLYING".
On the other hand, the formulas work on only two of examples without any wildcards: "FIRE" and "FIRE".
VLOOKUP and HLOOKUP
Both VLOOKUP and HLOOKUP functions support wildcard characters. Although both functions have an approximate match mode, using them in this mode may not return the correct result every time. Wildcards gives you more precision on your search. Use a string with a wildcard for lookup value argument to search.
The following screenshot shows an example for each formula. VLOOKUP function searches the "C*n" value, and matches with "Charmeleon". On the other side, HLOOKUP function searches a two-character string that matches "HP".
MATCH
MATCH function is another lookup function that support wildcard characters. Aside from returning a value on a different column, MATCH function returns the position of the found value. Once again, use wildcard characters in the lookup value argument.
SEARCH
You can use the SEARCH function with wildcards to find a string pattern in another string. In our example, we searched for a pattern like "a?to*e" to locate a string starts with "a", followed by any single character, which is followed by "to", and any number of characters until an "e" character is found.
