Wildcards are a very versatile set of symbols that help you select a group of similar strings at a time.
In this tutorial, I will cover what are wildcards in Google Sheets and how to apply them.
We will also take a look at a few use-cases where wildcards can be highly helpful.
What are Wildcards in Google Sheets?
Wildcards are special symbols that help maximize your search results by representing one or more string characters. Google Sheets lets you use three different wildcard symbols:
- The asterisk (*): This wildcard represents any number of characters, for example, the string ‘Sam’ could represent Sams, Sammy, Samsung, Samson, Samaritan, etc.
- The question mark (?): This wildcard represents a single character. For example, the string ‘S?m’ could represent Sam, Sum, Sim, Sem, Som, etc.
- The tilde(~): This wildcard usually precedes one of the above wildcard characters (* or ?) and is used to tell Google Sheets that the next character should not be considered as a wildcard, but as a regular character symbol. For example the string (t~*) means that we want to search for all strings with the exact text t*.
What are Wildcards Used For in Google Sheets?
Wildcards can be useful when you want to search or replace strings containing a particular character or a group of characters. You can add them to search strings and use them inside Google Sheets functions.
The most common applications of wildcards are in the following cases:
- In conditional functions like SUMIF, SUMIFS and COUNTIF
- To filter data based on a condition
- To do a partial lookup with the VLOOKUP feature
Let us take a look at some of the above use-cases to understand the power of wildcards.
Using Wildcards in a SUMIF Function
A smart way of using the SUMIF function is to incorporate wildcards into the condition part of the function. For example, say you have the following dataset containing Total Sales of different phone models:
If you want to find the total sales of all Samsung models, you can use the ‘*’ wildcard in your SUMIF function as follows:
- Select the cell where you want the result of the total sales to appear (D2 in our case).
- Type the following formula in the cell:
- Press the return key
This should display the sum of Total Sales of Samsung phones in cell D2.
Explanation of the Formula
In this example, the condition “Samsung*” means ‘find all cells that contain the word, Samsung’. It doesn’t have to be an exact match, but the cell should contain the word ‘Samsung’, along with any other character(s).
Once a match is found, the SUMIF function takes the Total Sales value corresponding to the matching cell and adds it to the list of selected Total Sales values. Once it completes going through all the models, the SUMIF function sums up the selected Total Sales values and displays the result in cell D2.
In other words, the wildcard ‘*’ helped find different variations of the search term ‘Samsung’ in column A.
You can use the question mark ‘?’ wildcard in the same way. The ‘?’ wildcard is used to represent a single character, anywhere in the word. So if you wanted to search for, say, all Apple iPhone X models, you can use “Apple iPhone X?” in the condition.
Using Wildcards to Filter Data Based on a Condition
Using wildcards can also make it really quick and easy to filter data based on a condition. For example, say you have the following dataset of machine models:
Let us say you want to filter the data so that only information about models starting with the letter C and ending with the number 1 are visible. This can be very easily accomplished by using the question mark wildcard, as follows:
- Select the range of cells that you want to filter (A1:B9 in our case).
- From the Data menu, select ‘Create a Filter’.
- Click on the filter icon next to the ‘Model’ header.
- In the menu that appears, select the ‘Filter by condition’ option.
- Click on the dropdown menu just below it and select the option ‘Text contains’.
- A new input box should appear just below the dropdown box. This is where you can enter a search string or formula for your filter.
- Type the search string ‘C?-??1’ in the input box.
- Click OK.
This will instantly filter the results and display only 3 rows corresponding to models CA-721, CB-231, and CA-111.
The question mark wildcard acts as a placeholder for any character in your search string. So, entering the string ‘C?-??1’ will only filter out the rows corresponding to models that have the given format: C followed by a character, followed by a hyphen, followed by two more characters, followed by a 1.
Since the last model, C-71B1 does not follow the same format, it is not included in the filter, even though it starts with a C and ends with a 1.
With the same methodology, you can use various criteria to filter results. For example, if you want to filter all the models that begin with C and contain the alphabet B in it, you can use the search string C*B. This will give only two results – models CB-231 and C-71B1.
Using Wildcards with VLOOKUP (Partial Lookup)
When you want to use VLOOKUP to find a value in the source table that isn’t an exact match, but a partial match, wildcards can be quite helpful.
For example, say you have the following source table of URLs, and you want to find the URL containing the name ‘amazon’ from the list:
You will notice from the above table that none of the URLs in the source table have an exact match for the name ‘amazon’. But it does exist as a partial match in cell A5. Using the VLOOKUP function to make a partial match like this is called a Partial Lookup.
Let us see how we can use wildcards to make a partial lookup for the lookup table shown below:
Here are the steps you need to follow:
- Select the cell where you want the lookup result to appear (D2 in our case).
- Type the following formula in the cell: =VLOOKUP(“*”&C2&”*”,$A$2:$A$8,1,FALSE).
- Press the return key. This will display the full URL of the cell containing the word ‘amazon’.
- Double click the fill handle to copy the formula to the rest of the cells of column D.
You should now get the list of all URLs from the source table that contains each of your search strings.
Explanation of the Formula
Instead of using the regular VLOOKUP formula:
We incorporated wildcards by flanking the cell reference C2 with an asterisk on both sides as follows:
This made sure the VLOOKUP function looked for any text in the source table that contains the word in cell C2, even if it has some characters before or after it.
So, the formula will look for a match and when it gets one, it returns the full URL corresponding to the given word.
Use-case for the Tilde Wildcard
The tilde wildcard (~) may not be used very often, but it’s good to know how to use it nonetheless.
As explained before, the tilde wildcard is used to tell Google Sheets that the next character should not be considered as a wildcard, but as a regular character symbol.
Let us take a small example to understand what this means.
Let us say you have the following dataset and want to count all the cells that contain exactly the word c*t only – not cat, and not cot.
Below is the formula that uses the asterisk wildcard to get the strings that start with C and end with T:
This is not what we wanted, as it counts all the words that start with C and end with T.
This is because the COUNTIF function sees the asterisk symbol is a wildcard, rather than a regular character symbol. So wherever it sees a cell containing a word that starts with c and ends with t, it counts that as a match.
To make sure the COUNTIF function counts only the cells containing the exact string ‘c*t’, we need to use the tilde wildcard (~) to escape the asterisk character, as follows:
This will now return 1 as the result since there is only one cell in the given range that contains the exact string c*t.
In this tutorial, we discussed wildcards in Google Sheets.
We discussed the three wildcards (asterisk, question mark, and tilde) that can be used in Google Sheets and demonstrated how to use them using three use-cases.
We hope this was helpful for you and encourage you to make use of the power of wildcards to refine your searches and conditions.
Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.