How to Use Wildcard Characters in Google Sheets
In this article, you will learn what wildcard characters are and how to use them in Google Sheets. You can use these wildcard characters to replace other characters. The partially replaced value can be a search keyword for a partial match in some functions such as COUNTIF, COUNTIFS, SUMIF, SUMIFS, and Conditional Formatting.
There are three types of a wildcard - “*”: asterisk, “?”: question mark, and “~”: tilde.
How do you use wildcard characters?
Understand what function each of the three signs has.
“?”: the question mark replace and represent any single character.
“*”: the asterisk can represent and take the place of any number of characters.
“~”: the tilde works differently from the other two wildcard characters. The tilde cancels the functions of the other two symbols, “?” and “*”, as wildcard characters and thus makes Google Sheets functions recognize the symbols as standard texts.
Assume you look at an inventory list and want to count the total number of inventory products that meet a specific criterion with the SUMIF function.
(i) Product type starts with “A-”
In this case, the standard of the SUMIF formula should be “A-*”. By putting the asterisk at the end of the text, this formula looks for a Product Type that begins with “A-”, followed by any number and type of characters such as “A-C 60***”.
The SUMIF function sums up the number of inventory of items highlighted in light red in the picture and provides 840.
(ii) Product Type starts with “?B”- Note: The question mark is used as a general text
In this example, the criterion should be “~?B*”. By putting the tilde next to the question mark to the left, the question mark is no longer a wildcard but just a symbol. The SUMIF formula adds up the volume of the inventory for times highlighted in light yellow in the screenshot and provides 320.
(iii) Product Type includes “A-X-C” - Note: X represents any single character here
The search keyword should be “*A-?-C*”. As “?”, the question mark can represent any single value or character. It should be put where any single character is acceptable. The SUMIF formula gives 160 as it picks up only one item, meeting the criterion.
(iv) Product Type includes “??” - Note: The double question marks are used as general texts
The double question marks need to be treated as general characters here. So, you need to put a tilde next to each question mark to the left to cancel their effect as wildcards. Also, you have to enclose them with asterisks so that the double question marks can have any number of characters before and/or after them.
That said, the criteria is “*~?~?*”. The SUMIF returns 600 as a total inventory amount of the product highlighted in the screenshot below.
(v) Product Type ends with “60***”- Note: The asterisks are used as general texts
Similar to the fourth example, you need to have the formula recognize the asterisks as general symbols. To do so, you can put on tilde next to each asterisk to the left. As any number of characters can be ahead of this keyword, you need to add an asterisk next to 60 to the left.
The criterion looks “*60~*~*~*”. The returned result is 510 as the total inventory amount of three types of products highlighted in the following picture.
