How To Use Wildcards In Google Sheets

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 use 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~?”

Knowing how to use wildcards in Google sheets is a beneficial 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.

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 ease the process. 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 S9 model
Example of spreadsheet to learn how to use wildcards in Google Sheets
Example spreadsheet

You can make a copy of the spreadsheet used in this tutorial with all the examples. Get it here.

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
  • Type in the following code 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 the Google Sheets to consider all the cells which have “iPhone” as their starting point. Hit enter to view the answer.
Output of 1st objective
The output of 1st objective
  • 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)
Fill in the formula as shown
  • The question mark (?) conveys the 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.
Output of 2nd objective
The output of 2nd objective

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 for data filtration
Example spreadsheet

Our objective is to create a filter that filters out Model IDs that start with “A?”. 

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
  • Click on the top menu and select Data -> Create a filter
Select data then create a filter
  • 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 will be Model ID.
Filter button appears on the header row of the selected column
  • Click on the Filter button. Now click on the Filter by condition option and select Text contains.
Click on filter by condition then click on Text contains
  • 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.
Type in the following text in textbox
  • This will convey 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. Select OK.
Select all then click on OK
  • You will see that your data has been filtered according to the criteria you have specified. 
The desired output
Final output using wildcards 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 powerful 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 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/