The 3 Google Sheets Wildcards and How to Use Them

Sometimes when performing calculations in Google Sheets or any other spreadsheet program, we don’t know exactly what string should fit into the formula. Also, we may need to perform searches that could cover any string. Luckily Google Sheets wildcards can help with both of these situations.

Read on to learn what a Google Sheets wildcard is, what they do, and how to use them.

What Is a Wildcard in Google Sheets?

Wildcards are unique symbols that can represent 1 or more string characters to help you get the most relevant search results possible. When you wish to return particular results, they are frequently used in conjunction with search functions, conditional functions, filtering, and lookup functions to make your searches more dynamic.

There are three distinct wildcard symbols available in Google Sheets. These are:

• Asterisk (*): As a wildcard, the asterisk can stand in for any symbol and number of characters.
• Question Mark (?): The question mark can represent one character in a string. For example, the string “T?m” could represent Tom, Tim, Tam, Tum, etc.
• Tilde (~): This character mostly comes before the asterisk or the question mark, and it is used to indicate that the proceeding character shouldn’t be considered as a wildcard but as a normal symbol to be used as a character. As an example, using the string “s~*” means that we wish to search for the exact “s*” text.

What Are Google Sheets Wildcards Used For?

Wildcards might be helpful when you search for or replace strings that include a certain character or set of characters. They may be used in Google Sheets functions and added to search strings.

The following situations are where wildcards are most frequently used:

• In conditional formulas such as COUNTIF, SUMIF, and SUMIFS.
• When filtering data based on a specified condition.
• Using VLOOKUP to perform a partial lookup.

Now, let’s look at some of the mentioned cases to understand the power of these wildcards.

How to Use Wildcards in Google Sheets

To understand the concept of wildcards in Google Sheets better, let’s look at a few examples.

SUMIF Wildcard

Using a wildcard in a SUMIF function is useful for adding specific data points.

Below we have a dataset that contains the sales of a few brands of cars. We only want to get the sales data for cars made by Honda in this case. As you can see in the data, the cars are written with their models as well as the manufacturer in the same box. Using a wildcard here means that only the car manufacturer needs to match for the condition to be fulfilled.

Here are the steps you have to follow to do this:

1. Click on the cell where you want the data to appear, which in this case is the cell B11.
2. Enter the initial part of the SUMIF formula, which looks like =SUMIF(.
3. Enter the first parameter, which is the range. Here, it is the cell range A2:A8.
4. After adding a comma, we will enter the criteria to test it. Here, we will write Honda*.
5. For the third parameter, we need to write the sum range, which contains the values to be summed. Here, it is the cell range B2:B8.
6. Finally, add a closing round bracket and press Enter to execute the formula.

This formula will then find all cells in this example that include the word “Honda” when the criteria “Honda*” is used. The cell must include the word “Honda,” including any character, even if it is not an exact match.

When a match is made, the SUMIF function adds the Number of Sales associated with the matching cell to the list of chosen Number of Sales. The SUMIF function adds the specified Number of Sales values after it has gone through all the cells, then it shows the result in cell B11.

Google Sheets Filter Wildcard

Using wildcards in Sheets can make it easy to filter the data in your spreadsheet based on a condition. We will be using the filter function in Google Sheets to do this in the following example.

In this example, we have a set of data containing names. Here, we wish to filter them so that only the names that start with the letter J and contain only four characters are displayed.

We can use either an asterisk or a question mark as a stand-in for the characters in the names.

Here are the steps you have to follow to do this:

1. Left-click and drag to select the cells where you wish to apply the filter. Here, it is the cell range A1:A8.
2. Click on Data in the main top bar of the screen. This will open a drop-down menu.
3. There, click on Create a filter.

4. This will highlight the cells you chose. A filter icon will come up on the right side of the first cell.
5. Click on this icon. This will open a small menu beside it.

6. We want to apply a condition to the filter for this particular example. Click on Filter by condition.
7. Now, we will select the filter we want to apply. In this example, we will choose the Text contains filter.
8. In the text box, type in the value you want to filter, which in this case is J???, J***, or even J*.
9. Click on the OK button to apply the changes.

Using a question mark as a placeholder means that you can be precise with the location to filter the data. Let’s assume that you want to find four-letter names in the same dataset with a “c” at the third position. To do this, you will use the same condition discussed above, but instead, you will write “??c?” in the text box. When executed in this dataset, the output will be “Jack”

Other Uses for Wildcards

The above examples are just a few ways that you can use wildcards. Here are a few other ways they work:

• Google Sheets QUERY wildcards
• Google Sheets wildcard IF functions
  • AVERAGEIF
  • COUNTIF
  • IFS
  • IFCONTAINS

• SUM functions like DSUM in Google Sheets
• Google Sheets find and replace wildcard searches

Frequently Asked Questions

How Do You Do a Wildcard in Google Sheets?

There are three wildcard characters in Google Sheets which include the tilde, asterisk, and question mark. These serve different purposes and are used to enhance the functionality of Google Sheets. You can use a question mark or asterisk to replace parts of text strings to perform effective searches.

How Do You Use Wildcards in COUNTIF?

In a COUNTIF wildcard search, the question mark symbol will match one character, while an asterisk symbol will match zero or more than zero characters of any type. The tilde symbol is called an escape wildcard and is used to match the other two wildcards that may appear in the data. This will count cells with partially matching or specific matching text.

What Does the Asterisk Mean in Google?

As a wildcard, Google Sheets can use the asterisk to stand in for any symbol as well as any number of characters. Essentially, it can replace a symbol or character in a search, which means that any character can take its place in the search. For example, b*t can search bit, but, bot, boot, etc.

How to Use Tilde in Sheets?

The tilde is used to match with a literal character. You use it when you want to perform a search with the actual symbol from a wildcard and not use it as a wildcard. These are the asterisk and the question mark operators.

You use a tilde when the cell contains these literal characters. The usage may look like “~*” in the case of an asterisk. For a question mark, the usage will look like “~?” and for a literal tilde, it will look like “~~”

Wrapping Up Our Google Sheets Wildcard Guide

Hopefully, the above examples gave you a taste of what wildcards can do. In short, they are perfect for searching non-specific text in various functions. Feel free to try them out in any function or Google Sheets tool that can search.