How To Use Wildcards In Google Sheets

Reading Time: 6 minutes

A quick guide:
How to use wildcards in Google Sheets:
1) There are 3 wildcards in Google Sheets: 
Question mark (?) : Represents one character only
Asterisk (*) : Represents multiple characters 
Tilde (~) : Used with ‘?’ and ‘*’ to treat them as normal characters
2) These wildcards are used as follows:
Let’s say, we need to find “S9” then use “S?”
Let’s say, we need to find “S9” and “S10” then use “S*”
Let’s say, we need to find “S?” then use “S~?”

Spreadsheet template with formulas here.

Knowing how to use wildcards in Google sheets is a useful skill that can differentiate spreadsheets users. Wildcards in google sheets are used in combination with other functions and can also be used for the data filtration process. This improves the efficiency of the logic-building process and makes the function more understandable.

Wildcards in Google Sheets

There are three wildcards in Google Sheets. They are as follows:

  • Question mark (?): The question mark wildcard represents a single character.
  • Asterisk (*): The asterisk wildcard represents any number of characters.
  • Tilde (~): The tilde wildcard is combined with other wildcards (* and ~) just to convey Google Sheets not to use these characters as wildcard characters. For example, In some situations, one would like to use ‘?’ or ‘*’ in Google Sheets. In those cases, we use ‘~?’ or ‘~*’ in Google Sheets to use them as standard characters. 

Wildcards can simplify the process of function writing. It’s widely used to filter data in Google Spreadsheets and is usually combined with other functions like SUMIF, VLOOKUP, etc. 

This tutorial’s prime objective is to learn how to use wildcards in Google Sheets. 

Using wildcards in Google Sheets along with SUMIF function

Wildcards are extensively used along with other functions to achieve results that the functions normally would not be able to do. Let’s look at an example of wildcards being used in combination with SUMIF function in Google Sheets.

Objectives: The objectives are as follows:

  • Objective 1: Calculate the total quantity of all models of the iPhone
  • Objective 2: Calculate the total quantity of the Galaxy S9 model
Example of spreadsheet to learn how to use wildcards in Google Sheets
Example spreadsheet

Note: This tutorial uses the SUMIF function for explaining wildcards. If you are not familiar with the SUMIF function then follow this quick tutorial.

We will now start with a step-by-step process to fulfil our objectives. The steps are as follows:

  • Select the cell next to objective 1 output to store our final result.
Select the empty cell to enter the wildcards formula in Google Sheets
  • Type in the following formula to get the total quantity of all the iPhone models.
=SUMIF(C4:C12, "iPhone*", D4:D12)
Fill in the formula as shown
  •  The asterisk (*) conveys to Google Sheets to consider all the cells which have “iPhone” as their starting values. Hit enter to return the result. The output is as shown in the image below.
Using wildcards in Google Sheets to find the total of specific products
  • Select the cell next to objective 2 output to store our final result. Type in the following code to get the total quantity of the S9 model.
=SUMIF(C4:C12, "S?", D4:D12)
Using wildcards in Google Sheets - first output
  • The question mark (?) wildcard signals to Google Sheets to consider all the cells which have “S” as their starting point. The question mark represents only one character after “S” which fulfils the criteria of our objective 2.
  • Hit Enter to view the answer.
Using wildcards in Google Sheets - output

Using wildcards in Google Sheets to filter data

Wildcards are extensively used in the process of data filtration. It helps in refining data and answering queries. It returns only those data fields that fulfil certain criteria. 

Let’s look at an example of using wildcards in Google Sheets for filtering data. 

Example spreadsheet to understand how to use wildcards in Google Sheets for data filtration

Our objective is to create a filter that returns only those phones with Model IDs that start with “A?”. This can easily be achieved using wildcards in Google Sheets. 

We will now start with a step-by-step procedure to accomplish our objective. The steps are as follows:

  • Select the range of cells that contains the data that you want to filter. In this case, we wish to filter Model ID.
Select the range of data fields including the header as shown - wildcards in Google Sheets
  • Click on the top menu and select Data -> Create a filter
Creating a filter to use wildcards in Google Sheets
  • A filter button appears on the header row of the selected column. Select the button in the top header row of the column you want to filter. In this case, it is Model ID.
Filter button appears on the header row of the selected column - how to use wildcards in Google Sheets
  • Click on the Filter icon. Now click on the Filter by condition option and select Text contains.
Adding a filter with wildcards in Google Sheets
  • Now we will be entering the format of the string that we want to filter for. Since we want to return IDs that start with ‘A?’. We will type “A~?*” in the textbox.
Filter texts with wildcards in Google Sheets
  • This will convey to Google Sheets not to treat the question mark as a wildcard. It returns any value that starts with an “A?”, followed by any number of characters. Click OK.
Adding filters with wildcards in Google Sheets
  • You will see that your data has been filtered according to the criteria you have specified. 
The final output using wildcards in Google Sheets for data filtration

We have filtered out the Model ID section and have only those IDs that start with “A?”. That’s how wildcards in Google Sheets ease up the data filtration process.

Conclusion

We have learned how to use wildcards in Google Sheets using illustrative examples. We have gone through the step-by-step process of using wildcards along with functions as well as for data filtration purposes. Now you are all set to start using this simple tool to improve your logic-building process along with the code readability.

Frequently Asked Questions

How many characters does the * wildcard replace?

Alternatively referred to as a wild character or wildcard character, a wildcard is a symbol used to replace or represent one or more characters. The most common wildcards are the asterisk (*), which represents one or more characters, and the question mark (?) which represents a single character.

Can you use a wildcard in an if statement?

Unlike several other frequently used functions, the IF function does not support wildcards. However, you can use the COUNTIF or COUNTIFS functions inside the logical test of IF for basic wildcard functionality.

How do you use wildcards in conditional formatting?

In addition to formulas, wildcard characters in Google Sheets can be used to create conditional formatting as well. Say, the task is to highlight the brands with names that begin with the letter A. Go to Conditional Formatting -> New Rule -> Select “Use a formula to determine which cells to format.”

See Also

Are you interested in learning more about how much you can succeed with Google Sheets? With so many powerful features of Google Sheets, you can save time and effort.

We have several tutorials that cover tricks and tips in Google Sheets. You can discover them here.

Here are some articles you might be interested in:

https://blog.tryamigo.com/regex-rules-and-syntax-in-google-sheets/

https://blog.tryamigo.com/regex-formulas-in-google-sheets/

https://blog.tryamigo.com/count-unique-values-in-google-sheets/

Leave a Reply