Excel wildcard is a special character that helps you find text values that are approximately similar but are not exactly the same. In other words, you can use fuzzy matching with wildcards.
In the example above, we want to summarize values in the sales column where the product name starts with ‘A.’ The formula looks like this:
=SUMIF(products, “a*”, sales)
In the range B3:B10, there are two records where the product name begins with “A”. As you see, the operation is not case-sensitive. Note: ‘products’ (range B3:B10) and ‘sales’ (range C3:C10) is a named range. Before we look at the wildcard characters, the most important thing is that wildcards only work with text values.
Excel Wildcard characters and Examples
As we mentioned above, wildcards only work with text. If you have numeric values, use logical operators in the formula. In Excel, you can use three different wildcards to build a formula.
Asterisk (*)
The asterisk character means any number of characters in the world of a wildcard search. So, for example, “Ap*” will match “Apple“, “Application“, “Appearance“, and so on.
In the picture below, you can see two typical cases.
- =SUMIF(products, “m”, sales) returns the sum of sales if the product name starts with “m”
- =SUMIF(products, “m”, sales) returns the sum of sales if the product name ends in “m”.
Question mark (?)
The question mark can replace a single character in the specified position.
- “melo?” matches “melon“,
- “le?on” will match “lemon“,
- “???on” matches “lemon” and “melon”
Tilde (~)
Tilde plays a special role in Excel formulas. This character ensures that Excel reads the next character as the original, not as a wildcard.
In the example, we want to sum the sales where the product name ends in “?”. However, we will use a workaround with searches because the question mark character is a wildcard.
- =SUMIF(products,“?”,sales)
- =SUMIF(products,“~?”,sales)
The first formula returns the total sales. The second formula gets the accurate result that we are waiting for.