Excel wildcards are a core feature that expands the range of criteria you can use in conditional functions. The most representative Excel conditional function is the IF function. In these conditional functions, logical operators and wildcards become increasingly useful as you learn more about them.
Types of WILDCARD
There are three types of wildcards used in Excel. Let’s look at how to set each one and the criteria it covers.
First, note that wildcards can only be used with TEXT values. For numbers, you can set criteria using logical operators.
- Asterisk (*) – Used to set any characters as criteria.
- Question mark (?) – Used to set the number of characters as criteria.
- Tilde (~) – Used when you want to use wildcard signs directly in criteria. (~*),(~?),(~~)
You can review an example of applying wildcards in the SUMIF function.
How to Use WILDCARD and Set Criteria
Let’s look at how to use (*), (?), and (~).
- ? : Used to set the number of characters as criteria.
Use “?” to set one character as the criterion.
(e.g. “A”,”B”,”C”,etc.)
Use “??” to set two characters as the criterion.
(e.g. “AA”,”BA”,”CA”,etc.)
Use “???*” to set three or more characters as the criterion.
(e.g. “AAA”,”BAAAA”,”CAAAAAA”,etc.)
You can set the number of characters as a criterion based on the number of question marks.
To set a character pattern as the criterion, use the format “???-???”.
(e.g. “123-456″,”ABC-EFG”,etc.) - An asterisk (*) is rarely used by itself. It is used when you need to set text containing a specific character or text string as the criterion.
To set text that begins with KIDS as the criterion, use the following syntax.
“KIDS*”
The syntax above sets all text beginning with KIDS as the criterion, such as “KIDS-outer”, “KIDS2”, and “KIDSinner”.
To set text that ends with T as the criterion, use the following syntax.
“*T”
The syntax above sets all text ending with T as the criterion, such as “TEXT”, “CAST”, and “TTT”.
To set a criterion containing specific text, use the syntax “*UUU*”.
(e.g. “1UUU-456″,”UUU”,”999UUU”,etc.) - The tilde (~) is used to set text containing ? and * as the criterion.
The function syntax is as follows.
“*~?”
(e.g. “Good?”,”No?”,etc.)
Conclusion
Along with logical operators, wildcards are among the most useful features in Excel conditional functions. If you frequently use functions based on the IF function, be sure to master them. If you also master absolute references, you can use any function in the format you want.