In Google Sheets, there are three wildcard characters that you can use:
- Asterisks
- Question Mark
- Tilde
In this tutorial, we will learn to use each wildcard character with an example in the real world.
Asterisks (*)
An asterisk is a wildcard character that reflects N number of characters in a string. If you use New*, it can refer to New York, New Jersey, and New Hampshire.
In this example, when you use it after the string “New”, the formula won’t consider any string which is there after the string “New”.
=COUNTIF(A2:A5,"New*")The only string to consider here is “New” which is fixed, and the rest is dynamic. And in the same way, you can also use an asterisk before a string or before and after the string.
Question Mark (?)
With Question Mark, you can replace a single character from a string. This is can useful in dealing with inconsistent data.
In the above example, we have used the COUNTIF to count the values where we don’t have a specific character between “Sheets” and “Mojo”. And that’s the reason we have used the a “?” question mark between strings.
=COUNTIF(A1:A3,"Sheets?Mojo")Tilde ~
The ~ tilde is to nullify the effect of any wildcard character you already have in the string. Let’s say you have a value that ends with an asterisk (98*) but is an actual value character.
When you use it in a formula, that formula will treat it as a wildcard character and consider all the values which start with 98. But when you use (98*~), this tells the formula to consider an asterisk (*) as a normal character and part of the main string.
Use of Wildcard Characters in Google Sheet
You can use Wildcard characters in formulas with strings. Apart from that, you can also use it with conditional formatting and with the Find and Replace dialog box. But its best use is with the formulas.