What Is Wildcard In Google Sheets?
The Wildcard in Google Sheets are symbols or special characters that help users to retrieve or group similar strings at a time. We have 3 different wildcards, and they are “*”, “?” and “~”. With the help of the Google Sheets Wildcard, we can maximize our search results by matching multiple strings in a dataset, even if it is a partial match, i.e., one or more string characters match with the selected cell value.
In this example, we have the items and their quantities. We will use a wildcard along with the SUMIF function to find the sum of the values that satisfy the criteria, where the item names have the “G” letter.
Select cell D2, enter the formula =SUMIF(A2:A6,“*G*”, B2:B6) and press “Enter”.
The cells A3, A4 and A5 have the letter G, their corresponding values are taken into consideration and their sum is returned as 850. Notice that the cell A5 has the letter “g” in-between the string “Bangles”, and due to the advantage of using a wildcard, i.e., “*”, we are able to maximize our search.
Key Takeaways
- The Wildcard In Google Sheets are special characters used as symbols in formulas that are of three types, namely, Asterisk (*), Question Mark (?) and Tilde (~).
- Asterisk (*) is used to match any number of characters in the word.
- Question Mark (?) is used to match any single character or pattern of the words.
- Tilde (~) is used to remove wildcard characters like an asterisk (*) and question mark (?) in Google Sheets.
- The asterisks are most commonly used to retrieve characters, question mark is also used rarely, but the Tilde is almost not used.
- We can retrieve data based on exact or partial match using the “*” wildcard find the sum using the SUMIF(), find the count using the COUNTIF(), and also use the VLOOKUP() function.
Types Of Google Sheets Wildcard
The three Types Of Google Sheets Wildcard are as follows:
- Asterisk (*)
- Question Mark (?)
- Tilde (~)
#1 ASTERISK (*): It matches any number of characters, whether the character is at the start, end or in-between in a word.
- Ex* – It can match the words that start with the characters ‘Ex’ and ends with any number of characters, such as Excellent, EXCEL, Experience, Experiment.
- *Ex – It can match the words that end with the characters ‘Ex’ and starts with any number of characters, such as Codex, FedEx, Duplex, Vertex, Complex.
- *Ex* – It can match words that contain the character ‘Ex’ regardless of whether it is at the start, end or in-between, such as Excellent, Text, Alexa, Dexter, Complex, etc.
#2 QUESTION MARK (?): It represents any single character in the word.
- P?t – It can match the words that start with the word ‘P’ and ends with the word ‘t’. For example, PAT, PUT, PET, and POT.
- ??-?? – It will match the string of words, i.e., ABC-CFF, DC-PC, A@#-S@#.
#3 TILDE (~): It is used to find the wildcard characters in the text and cancel the wildcards presence.
- For instance, assume we have the words Excellent and Excel in the worksheet. If we need to find the exact word “Excel”, we generally put in the asterisk (*) wildcard character to find the partial match, as ‘Excel*’. However, this ends up searching both the words ‘Excellent’ and ‘Excel’.
- To cancel the presence of the wildcard character asterisk (*), we can apply the wildcard character tilde (~) as ‘Excel~*’. It will cancel the presence of the wildcard asterisk (*) and ends up searching only Excel in the list.
Examples
We will consider some Wildcard in Google Sheets examples with SUMIF(), COUNTIF() and VLOOKUP() functions and also use wildcards to filter data based on a condition.
Example #1 – Using Wildcards in a SUMIF Function
Let’s consider the dataset that consists of fruits and their price and find the sum of the values of partial match of the criteria “Apple” using the wildcard character “asterisk = *”.
The steps to find the partial match using the SUMIF Text are,
Step 1: Select cell D2 and enter the formula =sumif(A2:A8,”*Apple”,B2:B8).
Step 2: Press “Enter”. We will get the output $1,050, as shown below. The criteria to check is for Apple and the cells A2, A4 and A7 has the word in their values. Therefore, the corresponding prices are totalled and returned as output.
Step 3: In cell D6, we will test the formula without the wildcard. So, the formula will be =sumif(A2:A8,”Apple”,B2:B8). The output is the exact match value, $500, as shown below.
Example #2 – Using Wildcards to Filter Data Based on a Condition
The dataset given below consists of employee name, ID and department. We will use wildcards to Filter Data Based on a Condition.
The steps to use Using Wildcards to Filter Data Based on a Condition are as follows:
Step 1: Select a column header we want to filter; here, column C, and click the “More” option on the ribbon, as shown below.
Step 2: Click the “Create a filter” option. Then we will get the filter icon on the headers or the first row, as shown below.
Step 3: We will now get the filter icon on the headers or the first row, as shown below. Click the filter icon to see the available options, as shown below.
Step 4: Click the “Filter by condition” option and we will get a new field below with a drop-down
Step 5: Select the “Text contains” option, a new field appears below, there type “*in” andclick “OK”, as shown below.
Now, the filtered dataset using the wildcard option is shown below.
Since, Finance and Marketing contains the letters “in”, the rows with those letters are filtered.
Example #3 – Using Wildcards with VLOOKUP (Partial Lookup)
The dataset consists of employee names, age, location, gender, etc. We will use Wildcards with VLOOKUP (Partial Lookup) to find a partial match and get the gender of a selected name.
The procedure to use Wildcards with VLOOKUP (Partial Lookup) is,
First, select cell C10 and enter the formula =VLOOKUP(“*”&B10&”*”,B2:E6,3,0), as shown.
Next, press “Enter”, to get the following output.
The logic is that “*”&C10&“*” searched for Joyce with any number of characters on either side and returned the gender as female.
Example #4 – Use-case for the Tilde Wildcard
The dataset consists of continent names that start and end with the letter “A”. We also have included 2 values with wildcard character in them. We will Use-case for the Tilde Wildcard along with the COUNTIF function and retrieve the count of the results that satisfy the criteria.
The steps to Use-case for the Tilde Wildcard are,
Step 1: First, let us use the formula without the Tilde Wildcard. Therefore, select cell C2, enter the formula =countif(A2:A10,”a*a”) and press “Enter”, to get the following output.
We get the output as 6, because the formula considers the “*” symbol as a wildcard character and counts all the values that start with the letter “a”.
Step 2: Now, let us use the formula with the Tilde Wildcard. Therefore, select cell C6, enter the formula =countif(A2:A10,”a~*a”) and press “Enter”, to get the following output.
The formula checks the exact value and returns the count as 2, because only 2 cell values are “a*a”3.
Important Things To Note
- Question mark (?) matches any single position of the text value.
- Tilde (~) is used to find wildcard characters like an asterisk (*) and question mark (?).
Frequently Asked Questions (FAQs)
The Asterisk (*) wildcard matches any number of characters in the selected word.
For instance, “Sun*” will match words like SUNGLASS, SUNFLOWER, SUNSHINE, and SUNSTONE. Similarly, “*James*” will match any number of characters on either side of the word “James”, i.e., James Bond, Robert James, James Fuller, etc.,
The symbols for Wildcard in Google Sheets are found on the keyboard as follows:
Asterisk (*) -We can enter this symbol by pressing the keys “Shift+8” or if we have a number pad, we can select the “*” symbol directly.
Question Mark (?) – We can enter this symbol by pressing the keys “Shift+/”
Tilde (~) – We can enter this symbol by pressing the keys “Shift+`” found before the number keys, before 1, or below the “esc” key.
Ampersand (&) is not a wildcard in Google Sheets. Instead, it is used to concatenate values and Wildcards in Google Sheets.
In MS Excel or Google Sheets, not all the functions support wildcards. The following functions support wildcards in their formulas, namely,
a. VLOOKUP
b. COUNTIF and COUNTIFS
c. SUMIF and SUMIFS
d. MATCH Google Sheets Function
e. AVERAGEIF
f. IF Google Sheets Function
g. SEARCH
Download Template
This article must be helpful to understand the Wildcard In Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Wildcard In Google Sheets. We learn how to use it to improve searches with different functions like SUMIF using examples. You can learn more from the following articles. –
Leave a Reply