Excel supports wildcard characters in formulas to find values that share a simple pattern. For example, if you are looking for a string with known ending or beginning, and unknown characters in the middle, you can use wildcard characters to tell Excel to look for all compatible matches. In this article, we're going to show you how to use a wildcard in Excel formula.
How to use a wildcard in Excel formula
In total, there are 3 wildcard characters you can use in Excel. You can use 2 of them as a replacement of characters, and the third one to prevent the other 2 from being registered as wildcard characters.
- ? character stands for a single character. For example, X-M?n finds both X-Men and X-Man.
- * character stands for one or more characters. For example, s*man finds both Spider-Man and Superman
- ~ character that followed by ?, *, ~ characters searches the following character without using it as a wildcard. For example, X-M~?n finds only X-M?n.
All characters can be used in conjunction. For example, s*??n text finds Spawn, as well as Spider-Man and Superman.
You can use wildcard characters inside various functions. In the example bewlo, we used the VLOOKUP function to find the name of the publisher of a fictional character that ends with "boy". To find a text that ends with "boy", we used an asterisk (*) as a replacement for any characters before "boy". ;
=VLOOKUP("*boy",B3:C8,2,0)
Below is a list of formulas you can use with wildcards.
- AVERAGEIF
- AVERAGEIFS
- COUNTIF
- COUNTIFS
- HLOOKUP
- MATCH
- SEARCH
- SUMIF
- SUMIFS
- VLOOKUP
