There’s a hidden power in Google Sheets formulas that can save you time and make your data analysis more efficient. Introducing wildcard characters! In this article, I’ll walk you through the three main wildcard characters in Google Sheets and show you how to use them to your advantage.
The Power of Wildcards
Google Sheets has three wildcard characters that you can use with certain functions: the tilde (~), the asterisk (*), and the question mark (?). These special symbols can represent or replace single or multiple characters in your formulas, giving you more flexibility in data manipulation.
Before we dive into the examples, let’s clarify the difference between the computer technology term “wildcard” and the sports event term “wild card.” In the context of Google Sheets, we’re referring to the wildcard character used in formulas.
The Three Wildcard Characters
Here’s a breakdown of the three wildcard characters and their purposes:
- Asterisk (*): Matches a variable number of characters.
- Question Mark (?): Matches a single character.
- Tilde (~): Serves as an escape character for another wildcard, transforming it into a literal character.
Now, let’s explore some examples to help you understand how to use wildcards effectively in Google Sheets formulas.
Wildcards in Action
In the following examples, we’ll be using the SUMIF and SUMIFS functions to showcase the use of wildcards. However, you can apply similar techniques to other supported functions as well.
Imagine we have a dataset with columns for salespersons, areas of operation, products sold, dates of sale, quantity sold, unit rate, and amount. We’ll use this sample data to demonstrate the power of wildcards.
SUMIF Partial Match
Let’s start with the SUMIF function, which allows us to perform a criterion-based sum. Here’s the syntax: SUMIF(range, criterion, [sum_range]).
Using the Question Mark Wildcard
The question mark wildcard represents a single character. Take a look at this example:
=SUMIF(C2:C11, "TV 220?", G2:G11)
In this formula, the wildcard character is used to match any single character in place of the question mark. It will sum the ‘amount’ if the ‘product’ matches “TV 2202” or “TV 2203” based on our sample data.
Using the Asterisk Wildcard
The asterisk wildcard represents a variable number of characters. Check out these examples:
=SUMIF(C2:C11, "TV*", G2:G11)
This formula will sum the ‘amount’ if the ‘product’ starts with “TV” followed by any number of characters. It includes “TV2202”, “TV 2203456”, or any word starting with “TV.”
=SUMIF(C2:C11, "TV 2*3", G2:G11)
In this formula, the ‘product’ can be any text but should start with “TV 2” and end with “3”. For example, “TV 244444443” or “TV 2111113”.
Using the Tilde Wildcard
The tilde wildcard serves as an escape character for other wildcards. It allows you to treat them as literal characters. Here’s an example:
=SUMIF(B2:B11, "No~?th", G2:G11)
In this formula, the “?” is considered a literal character instead of a wildcard. It will sum the ‘amount’ if the ‘area’ is “No?th”.
SUMIFS Partial Match
Similar to SUMIF, SUMIFS allows for summing based on multiple criteria. Let’s explore its wildcard capabilities.
Using the Question Mark Wildcard
The question mark wildcard is also useful in SUMIFS. Consider this example:
=SUMIFS(G2:G11, B2:B11, "North", C2:C11, "TV 22??")
In this formula, we’re summing the ‘amount’ if the ‘area’ is “North” and the ‘product’ matches “TV 2202” or any text starting with “TV 22” followed by two more characters.
Using the Asterisk Wildcard
The asterisk wildcard works similarly in SUMIFS as it does in SUMIF. Take a look at this example:
=SUMIFS(G2:G11, B2:B11, "*East", C2:C11, "TV*")
This formula sums the ‘amount’ if the ‘area’ is either “North East” or “South East” (based on our sample data) and the ‘product’ starts with “TV”.
Using the Tilde Wildcard
The tilde wildcard also has a unique role in SUMIFS. Consider this example:
=SUMIFS(G2:G11, B2:B11, "No~?th", C2:C11, "TV*")
In this formula, the tilde (~) ensures that the “?” in “No?th” is treated as a literal character. It will sum the ‘amount’ if the ‘area’ is “No?th” and the ‘product’ starts with “TV”.
Wildcards in Other Functions
The examples above demonstrate the use of wildcards in SUMIF and SUMIFS. However, you may be wondering if you can use them in other Google Sheets functions.
While most formulas that use criteria allow for wildcard characters, there are exceptions and additional steps. For example, the FILTER function doesn’t support wildcards directly, but you can use the SEARCH function as a workaround.
In XLOOKUP and XMATCH, two modern lookup functions, you can use wildcard characters in “search mode 2”. Additionally, the IF logical function doesn’t support wildcard characters, but you can find an alternative approach in our resources section.
Conclusion
Wildcards can be incredibly powerful tools in Google Sheets formulas. By using the tilde, asterisk, and question mark as your secret weapons, you can perform partial matches and save time on data analysis.
Remember, the examples shared here are just the tip of the iceberg. Experiment with these wildcard characters and explore their possibilities. Happy Sheets adventuring!
This article is brought to you by Crawlan.com, your go-to source for SEO expertise and digital marketing secrets.
