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
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.
- Type in the following formula to get the total quantity of all the iPhone models.
=SUMIF(C4:C12, "iPhone*", D4:D12)
- 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.
- 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)
- 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 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.
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.
- Click on the top menu and select Data -> 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 is Model ID.
- Click on the Filter icon. Now click on the Filter by condition option and select 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.
- 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.
- You will see that your data has been filtered according to the criteria you have specified.
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/