Wildcard Characters
Wildcard characters are special characters that can replace other characters in a search:
- *: replaces no, one, or multiple characters
- ?: replaces one character
- ~: negates the effect of * or ?
Usage Example
In this example, the objective is to sum all the apples:
Use the SUMIF function and complete it:
=SUMIF(A2:A9,D2,B2:B9)
You can see that it does not work here because the function searches for cells that exactly match "Apple," and there are none.
To search for all cells containing "Apple," add the character "*" before and after, "*Apple*":
This time, the function correctly performed the sum of the 3 lines of apples.
To avoid having to enter these characters for each search, you can add them directly to the formula:
=SUMIF(A2:A9,"*"&D2&"*",B2:B9)
Advanced Usage Example
In this second example, the objective is to use the 3 wildcard characters to fully understand their utility.
We start with the XLOOKUP function and a search without wildcard characters:
=XLOOKUP(D2,A2:A9,B2:B9,"",2)
The goal now is to target a fruit that ends with a letter followed by a "?" (thus targeting "Apple A?").
So let's start with a "*", then a space, then a "?" to replace a letter, then the character "?" and see what happens:
The search "* ??" matches any fruit that ends with a space followed by 2 characters (in this case, "Apple G+" is the first cell to match this search).
To get a fruit that ends with a "?", we must ensure that this character is not considered a wildcard character, and to do this, it must be preceded by a "~":
This time, it is indeed the quantity of "Apple A?" that is returned.
Functions
The use of wildcard characters is only available with certain Google Sheets functions such as: