Wildcards, an incredibly adaptable collection of symbols, can assist you in selecting groups of similar strings simultaneously.
This guide will delve into what wildcards in Google Sheets entail and the methods to utilize them.
We will further explore several scenarios where wildcards prove to be immensely beneficial.
What are Wildcards in Google Sheets?
Wildcards are exceptional symbols that enhance your search outcomes by symbolizing one or multiple string characters. Google Sheets permits the usage of three distinct wildcard symbols:
The asterisk (*): This wildcard stands for any series of characters. For instance, the string ‘Sam’ could denote Sams, Sammy, Samsung, Samson, Samaritan, etc.
The question mark (?): This wildcard represents a solitary character. For instance, the string ‘S?m’ could denote Sam, Sum, Sim, Sem, Som, etc.
The tilde(~): This wildcard usually comes before one of the aforementioned wildcard characters (* or ?) and informs Google Sheets that the succeeding character should not be considered as a wildcard, but rather a regular character symbol. For instance, the string (t~*) indicates that we aim to search for all strings containing the exact text t*.
What are the Uses of Wildcards in Google Sheets?
Wildcards come in handy when your objective is to search or replace strings that contain a particular character or a group of characters. You can include them in search strings and incorporate them into Google Sheets functions.
Wildcards are most commonly used in the following instances:
- In conditional functions such as SUMIF, SUMIFS, and COUNTIF
- To filter data based on certain conditions
- To conduct a partial lookup using the VLOOKUP feature
Let’s delve into some of these instances to truly understand the power of wildcards.
Using Wildcards in a SUMIF Function
A clever approach to using the SUMIF function involves embedding wildcards into the condition component of the function. Suppose you have the following dataset containing Total Sales of different phone models:
To determine the total sales of all Samsung models, you can use the ‘*’ wildcard in your SUMIF function as follows:
- Choose the cell where you want the total sales result to appear (in our case, D2).
- Enter the following formula into the cell: =SUMIF(A2:A9, “Samsung*”,B2:B9)
- Press the return key
This will display the sum of Total Sales of Samsung phones in cell D2.
Explanation of the Formula
In this scenario, the condition “Samsung*” signifies ‘find all cells that include the word Samsung’. It’s not necessary for it to be an exact match, but the cell must include the word ‘Samsung’, along with any other character(s).
Upon finding a match, the SUMIF function takes the Total Sales value corresponding to the matching cell and adds it to the selected Total Sales values list. After going through all the models, the SUMIF function adds up the chosen Total Sales values and displays the result in cell D2.
In essence, the wildcard ‘*’ helped find different variations of the search term ‘Samsung’ in column A.
The question mark ‘?’ wildcard can be used in a similar manner. The ‘?’ wildcard is used to represent a single character, anywhere in the word. So if you wanted to search for all Apple iPhone X models, for example, you can use “Apple iPhone X?” in the condition.
Using Wildcards to Filter Data Based on a Condition
Wildcards also make filtering data based on a certain condition a breeze. For instance, suppose you have the following dataset of machine models:
Let’s assume you want to filter the data so that only information about models beginning with the letter C and ending with the number 1 are visible. This can be easily achieved 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, choose ‘Create a Filter’.
- Click on the filter icon next to the ‘Model’ header.
- In the menu that appears, choose the ‘Filter by condition’ option.
- Click on the dropdown menu just below it and choose the ‘Text contains’ option.
- 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.
- Enter the search string ‘C?-??1’ in the input box.
- Click OK.
This will promptly filter the results and display only 3 rows corresponding to models CA-721, CB-231, and CA-111.
The question mark wildcard serves as a placeholder for any character in your search string. Therefore, entering the string ‘C?-??1’ will only filter out the rows corresponding to models that follow the given format: C followed by a character, followed by a hyphen, followed by two more characters, followed by a 1.
The last model, C-71B1, is not included in the filter since it does not follow the same format, even though it starts with a C and ends with a 1.
Using this 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 yield only two results – models CB-231 and C-71B1.
Using Wildcards with VLOOKUP (Partial Lookup)
Wildcards can be beneficial when you want to use VLOOKUP to find a value in the source table that isn’t exactly the same, but rather a partial match.
For example, suppose you have the following source table of URLs, and you want to find the URL containing the word ‘amazon’ from the list:
From the above table, you’ll notice that none of the URLs in the source table exactly match the word ‘amazon’. But it does exist as a partial match in cell A5. Using the VLOOKUP function to make a partial match like this is referred to as a Partial Lookup.
Let’s see how we can use wildcards to make a partial lookup for the lookup table shown below:
Follow these steps:
Choose the cell where you want the lookup result to appear (D2 in our case).
Enter 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 have a list of all URLs from the source table that contains each of your search strings.
Explanation of the Formula
Rather than using the regular VLOOKUP formula:
=VLOOKUP(C2,$A$2:$A$8,1,FALSE)
We included wildcards by sandwiching the cell reference C2 with an asterisk on both sides as follows:
=VLOOKUP(“*”&C2&”*”,$A$2:$A$8,1,FALSE)
This ensures that the VLOOKUP function searches 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 search for a match and when it finds one, it returns the full URL corresponding to the given word.
Leveraging Wildcards in VLOOKUP for a Partial Lookup
In instances where you seek a value in your data set that isn’t an exact match but bears some similarities, the VLOOKUP function aided by wildcards can be of immense value.
Suppose you’re dealing with a source table of URLs, and your task is to single out the URL that includes the term ‘amazon’:
In this example, a cursory glance at the URLs reveals that none of them provides an exact match for ‘amazon’. However, a partial match is evident in cell A5. Using VLOOKUP for such a partial match is referred to as a Partial Lookup.
Let’s examine how we can deploy wildcards for a partial lookup in the context of the lookup table shown below:
To achieve this, follow these steps:
1. Select the cell where you want the lookup result to appear (D2 in our case).
2. Input the following formula in the cell: =VLOOKUP(“*”&C2&”*”,$A$2:$A$8,1,FALSE).
3. Hit the return key. This action will reveal the full URL of the cell that contains the term ‘amazon’.
4. Double-click on the fill handle to clone the formula for the remaining cells in column D.
This method should yield a list of all URLs from the source table that include each of your search terms.
Elucidation of the Formula
Instead of employing the conventional VLOOKUP formula:
=VLOOKUP(C2,$A$2:$A$8,1,FALSE)
We have opted to augment our cell reference C2 with asterisks on either side:
=VLOOKUP(“*”&C2&”*”,$A$2:$A$8,1,FALSE)
The implication of this is that the VLOOKUP function will search for any text in the source table that encompasses the term in cell C2, irrespective of whether there are characters preceding or succeeding it.
Thus, the formula will search for a match, and upon finding one, it will return the complete URL corresponding to the given word.
Exploring the Tilde Wildcard Use-case
Though the tilde wildcard (~) may not be as frequently used, understanding its functionality is valuable nonetheless.
As previously outlined, the tilde wildcard is deployed in Google Sheets to signify that the ensuing character should not be recognized as a wildcard, but instead as a standard character symbol.
For clarity, let’s explore a brief example:
Assume you have the following data set and your goal is to count all the cells that contain precisely the word ‘c*t’ – not ‘cat’, and not ‘cot’.
Here is the formula that uses the asterisk wildcard to obtain the strings that initiate with C and terminate with T:
=COUNTIF(B2:B7,”c*t”)
This is not the desired outcome, as it counts all the words that begin with C and conclude with T.
This happens because the COUNTIF function interprets the asterisk symbol as a wildcard, rather than a regular character symbol. Hence, whenever it encounters a cell containing a word that commences with c and concludes with t, it deems that a match.
To ensure the COUNTIF function counts only the cells housing the precise string ‘c*t’, we need to employ the tilde wildcard (~) to bypass the asterisk character:
=COUNTIF(B2:B7,”c~*t”)
Now, the result will be 1 since only one cell in the specified range contains the exact string c*t.
This tutorial has delved into the use of wildcards in Google Sheets.
We’ve looked at the trio of wildcards (asterisk, question mark, and tilde) that are available in Google Sheets and illustrated their usage in three distinct cases.
We hope this guide has been of assistance and encourage you to tap into the potential of wildcards to refine your searches and conditions.