HomeGoogle DocsSpreadsheetThree Main Wildcard Characters in Google Sheets Formulas

Three Main Wildcard Characters in Google Sheets Formulas

Published on

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

Please do not confuse the term ‘wildcard’ with the term ‘wild card.’ The former applies to computer technology (wildcard character), whereas the latter has a different meaning in sports events (for example, wild card entry).

This tutorial focuses on the use of the tilde, asterisk, and question mark symbols (special characters) in formulas within Google Sheets.

These individual wildcard characters (the three symbols mentioned above) can represent or replace single or multiple other characters in formulas. Here are their purposes.

  • Asterisk (*): Matches a variable number of characters.
  • Question Mark (?): Matches a single character.
  • Tilde (~): Serves as an escape character for another wildcard when positioned in front of it, transforming it into a literal character instead.

Let me take you through a few examples to help you understand the proper use of wildcards in Google Sheets.

Wildcards in Google Sheets

We can learn how to use the three wildcard characters mentioned above in Google Sheets formulas, rather than in the search or find and replace command

I am using the basic forms of two Google Sheets functions in the examples to help you understand the use of wildcards in Google Sheets.

Additionally, there are separate tutorials for the advanced use of some of the symbols mentioned above, which you can find in the last part of this tutorial.

In the examples below, I will be using the symbols with the SUMIF and SUMIFS functions. You can follow the same procedures to apply wildcards in other supported functions. Hence, I will not repeat the steps for every function here.

We will use the following sample data (please refer to the screenshot below) for the examples. It contains the names of salespersons, the area they operate in, the products they sell, the date of sale, quantity sold, unit rate, and amount, arranged in columns A to G. You can click on the button immediately after the image to obtain a copy of my sample sheet.

Wildcard Characters in Google Sheets Functions - Sample Data

Sample Sheet

SUMIF Partial Match

Are you looking for a criterion-based sum? Then, this is the function you are searching for! No doubt, the QUERY function is available, and even SUM + FILTER will also work. However, SUMIF is simple to understand and use.

Syntax: SUMIF(range, criterion, [sum_range])

Use of Wildcard Character Question Mark (?) with SUMIF

The question mark wildcard character in the SUMIF formula in Google Sheets can represent or take the place of any single character in the criterion part.

We can use the symbol twice (or more) in case we want to represent two unknown characters. You can find both examples below.

=SUMIF(C2:C11, "TV 2202", G2:G11)

The above SUMIF formula is used to sum the ‘amount’ if the ‘product’ is “TV 2202”. No wildcard character is used in this Google Sheets formula.

Here is a slightly different formula that contains the question mark symbol.

=SUMIF(C2:C11, "TV 220?", G2:G11)

This formula will sum the ‘amount’ if the ‘product’ matches “TV 2202” or “TV 2203” based on our sample data.

The last character is not important here, but there should be one character followed by “TC 220”. That’s important.

With one more example, we can conclude this part. Here is a formula having multiple wildcards in Google Sheets.

=SUMIF(C2:C11, "TV 22??", G2:G11)

Here, the text can be “TV 2201” or “TV 2202”. What’s important here is that there should be two characters, no matter which two characters, followed by “TV 22”.

Use of Wildcard Character Asterisk (*) with SUMIF

The asterisk symbol can represent or take the place of any number of characters in the criterion part in SUMIF.

Here are two examples:

=SUMIF(C2:C11, "TV*", G2:G11)

The above formula is used to sum the ‘amount’ if the ‘product’ is “TV2202”, “TV 2203456”, or any word starting with “TV.”

In the formula below, it can be any text but should start with “TV 2” and end with “3”. That means “TV 244444443”, “TV 2111113”, etc.

=SUMIF(C2:C11, "TV 2*3", G2:G11)
Partial match in SUMIF

Use of Wildcard Character Tilde (~) with SUMIF

The use of this wildcard character in Google Sheets (inside the SUMIF function) is entirely different compared to the above two and can be a bit confusing.

Sometimes, when your text includes “?” or “*” as regular characters, you can prefix them with a tilde (~) to make them literal characters instead of wildcards.

In the following example, the ‘?’ is considered a literal character, not a wildcard character.

=SUMIF(B2:B11, "No~?th", G2:G11)

SUMIFS Partial Match

Similar to SUMIF, for summing based on multiple criteria, we can rely on SUMIFS, SUM + FILTER, or QUERY.

Syntax: SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])

Here are some examples of partial matching in SUMIFS in Google Sheets.

Use of Wildcard Character Question Mark (?) with SUMIFS

Similar to SUMIF, this wildcard character can represent or take the place of any single character.

=SUMIFS(G2:G11, B2:B11, "North", C2:C11, "TV 2202")

In the above SUMIFS formula, no wildcard characters are used. The formula is to sum the ‘amount’ if the ‘area’ is “North” and the ‘product’ is “TV 2202”.

What about this one?

=SUMIFS(G2:G11, B2:B11, "North", C2:C11, "TV 22??")

Here I have used the question mark wildcards in Google Sheets with one of the SUMIFS conditions (criterion).

This formula is to sum the ‘amount’ if the ‘area’ is “North” and the ‘product’ is “TV 2202” or any text, but the starting characters should be “TV 22” followed by two more characters.

Use of Wildcard Character Asterisk (*) with SUMIFS

This wildcard character in SUMIFS can represent or take the place of any number of characters.

Straight to one example.

=SUMIFS(G2:G11, B2:B11, "*East", C2:C11, "TV*")

This SUMIFS formula sums the ‘amount’ if the ‘area’ is either “North East” or “South East” (as per our sample data) and the ‘product’ is any text starting with “TV”.

Use of Wildcard Character Tilde (~) with SUMIFS

The following formula sums the ‘amount’ if the ‘area’ is “No?th” and the ‘product’ is any item starting with “TV”:

=SUMIFS(G2:G11, B2:B11, "No~?th", C2:C11, "TV*")

If you haven’t specified the tilde, the formula matches “North” along with “No?th” and returns a different output.

Wildcards in Other Functions in Google Sheets

The examples above demonstrate the use of wildcards in SUMIF and SUMIFS in Google Sheets.

What about other Google Sheets functions?

You may be able to use the above three special characters for partial matches in most formulas that use criteria. However, there are exceptions and additional steps.

In XLOOKUP and XMATCH, two modern lookup functions, you can use wildcard characters, but only in ‘search mode 2.’

The FILTER function uses criteria for filtering tables, but it doesn’t support wildcards. However, you can achieve that with a workaround using the SEARCH function. You may also be able to use REGEXMATCH instead of SEARCH.

Additionally, the IF logical function doesn’t support the above special characters. In the “Resource” section below, see how to address this issue.

Resources

The following tutorials discuss partial matching in Google Sheets. Some formulas in them utilize wildcard characters, while others employ the SEARCH or REGEXMATCH functions. I hope you find them useful.

  1. Partial Match in IF Function in Google Sheets As Wildcard Alternative
  2. Partial Match in Vlookup in Google Sheets [Text, Numeric, and Date]
  3. CONTAINS Substring Match in Google Sheets Query for Partial Match
  4. Lookup Last Partial Occurrence in a List in Google Sheets
  5. Wildcards in Vlookup Search Range in Google Sheets
  6. LIKE String Operator in Google Sheets Query and Wildcard Use
  7. Filter Out Matching Keywords in Google Sheets – Partial or Full Match
  8. Regexmatch in Filter Criteria in Google Sheets [Examples]
  9. REGEXMATCH in SUMIFS and Multiple Criteria Columns in Google Sheets
  10. How to Use Wildcards in Sumproduct in Google Sheets
Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

4 COMMENTS

  1. Use wildcard in Vlookup referencing a cell instead of string and unfortunately it’s not working.

    Do you have any recommendations on using wildcards * with cell reference? Also, I went to the download page to order the ebooks from Amazon was not able to find the books on Amazon.

LEAVE A REPLY

Please enter your comment!
Please enter your name here