Table of Contents
What Are The Types of Excel Wildcard Characters?
In Excel, you have 3 different wildcard characters Asterisk, Question Mark, and Tilde. And each of these has their own significance and usage.
Ahead we’ll discuss each of these characters in detail so that you would be able to use them in different situations.
An asterisk is one of the most popular wildcard characters.
You can find any number of characters (in sequence) from a text. For example, if you use Pi* it will give you words like Pivot, Picture, Picnic etc.
If you notice, all these words have Pi in starting and then different characters after that. Let’s understand its working with an example.
Below you have a data where you have names of cities and their classification in a single cell.
Now from here, you need to count the number of cities in each classification. and for this, you can use an asterisk with COUNTIF.
In this formula, you have combined classification text from the cell with an asterisk. So, the formula only matches the classification text in the cell and the asterisk works as rest of the characters.
And in the end, you get the count of cities according to classification.
2. Question Mark
With a question mark, you can replace one single character from a text. In simple words, it helps you get more specific, still using a partial match.
For example, if you use champs it will return champs and chimps from the data.
3. Tilde (~)
The real use of a tilde is to nullify the effect of a wildcard character. For example, let’s say you want to find the exact phrase pivot*.
If you use pivot* as a string, it would give you any word that has champs at the beginning (such as pivot table, pivot chart).
To specifically the string pivot*, you need to use ~. So you string would be pivot~*. Here, ~ ensures that Excel reads the following character as is, and not as a wildcard.