How to Use Wildcard Characters in Google Sheets Functions

Knowing how to use wildcard characters in Google Sheets is useful when you want to represent or replace single or multiple other characters in Google Sheets functions.

There are three wildcard characters you can use with certain Google Sheets functions. Those are ‘?’ (question mark), ‘*’ (asterisk), and ‘~’ (tilde).

• Question mark (‘?’) is used to represent or take the place of any single character.
• Asterisk (‘*’) is used to represent or take the place of any number of character.
• Tilde (‘~’) has different use than the other two wildcard characters. It is used to tell the Google Sheets functions that * or ? are normal characters (since sometimes you may have them in your text). So, if you write ‘~*’ or ‘~?’, the * and ? are normal characters, and not wildcard characters.

Let’s take an example.

Say you own a store that sells mobile phones and have a list of all the mobile phones in the store and in the warehouse 📱

And now you need to know how many of each mobile phone you have. For this, we can use the SUM or SUMIF function.

When writing the SUMIF function, you will have to enter the criterion (the pattern or test to apply to the range). Each cell in the range will then be checked against the criterion for equality (or match, if you used wildcards when entering the criterion).

In our example, your criterion can be the model of the mobile phone. You can use ‘S20’ as your criterion if you need to know how many of Samsung Galaxy S20 mobile phones you have in the store and in the warehouse. But now you need to know how many of all of the Samsung Galaxy S mobile phone models you have.

So how do we do that?

Simple. You will do this by using a wildcard character in your criterion. Do you know which wildcard character you should use? We should use the asterisk (‘*’).

Let’s take a look at the real example where we will show you how to use wildcard characters in Google Sheets functions.

A Real Example of Using Wildcard Characters in Google Sheets Functions

To know the quantity of each mobile phone model, we will need column C (with the model) and column E (with the quantity).

Let’s first take a look at how many Samsung Galaxy S7 mobile phones we have. We will use the following formula =SUMIF(C2:C9,”S7″,E2:E9), where SUMIF() is our formula, C2:C9 is the range which is tested against the criterion, “S7” is the criterion, and E2:29 is the range to be summed. There are 34 Samsung Galaxy S7 mobile phones in the store and in the warehouse.

Let’s now see how many Samsung Galaxy S mobile phones we have. As said before, we will use one of the wildcard characters for this. But which one? If we use the question mark (‘?’), the formula will look only for mobile phone models that have one character after the letter ‘S’ (which is only S7). The result will once again be 34 since this is how many Samsung Galaxy S7 mobile phones we have.

But if we use the asterisk (‘*’), the formula will look for mobile phone models that have any number of character after the letter ‘S’ (which are S7, S10, and S20). The result will now be 64 since we have 34 Samsung Galaxy S7, 16 Samsung Galaxy S10, and 14 Samsung Galaxy S20 phones.

How to Use Wildcard Characters in Google Sheets Functions

Now we will show you how to use wildcard characters in Google Sheets, step-by-step.

1. First, click on a cell to make it active. For this guide, we will use cell B14.

2. Now, we should start off our function with the equals sign ‘=’ and enter the name of the function we will use (remember that you cannot use wildcard characters with all Google Sheets functions). The name of the function we will use in this guide is SUMIF. As you start typing, Google Sheets will automatically suggest functions that start with the same letters. Choose from the suggestions (just be careful) or continue typing.

3. After the opening round bracket ‘(‘, enter your range which is tested against the criterion. This range is C2:C9. Put a comma ‘,’ after it.

4. Now we should enter our criterion, which is “S*” (remember that it must be enclosed in quotation marks “”). Put a comma ‘,’ after it.

5. And finally, enter the range to be summed (sum_range), which is E2:E9. Enter the closing round bracket ‘)’ or press the Enter key on your keyboard. If you did everything according to instruction, the result will be 64.

Let’s now see what will happen if we use the question mark (‘?’). The formula will now look like this =SUMIF(C2:C9,”S?”,E2:E9) and will return the number of mobile phone models that have one character after the letter ‘S’ (in our example it is only S7). The result will be 34.

But what if we had some unlabeled boxes in the store and in the warehouse and need to know how many unlabeled boxes (S? in our spreadsheet) there are? If we write our formula like =SUMIF(C2:C9,”S?”,E2:E9) it will look for all of the mobile phone models that have one character after the letter ‘S’ (which are not only S? but S7, as well). The result will now be 70.

But this is not what we wanted. We need only the number of unlabeled boxes. So, how do we that? Simple. We will use our third wildcard character, tilde ‘~’. The formula will be =SUMIF(C2:C9,”S~?”,E2:E9) where tilde tell the formula that the question mark is an actual character and not a wildcard. The result will now be 36.

That’s it! Now you know how to use wildcard characters in Google Sheets functions!

You can make a copy of the spreadsheet using the link below and practice some more:

Or use wildcard characters with other Google Sheets formulas to sort and filter your data more effectively! 🙂

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe