How to Use Wildcard Characters in Google Sheets Functions

0
143
Wildcard Characters - Google Sheets

There are three main wildcard characters in Google Sheets that you can use with certain Google Sheets functions. Then what are those so called wildcard characters? They are ~ (tilde), * (asterisk) and ? (question mark). These single wildcard characters can represent or replace with single or multiple other characters in functions. Then how to use wildcard characters in Google Sheets functions? That I will explain you with the help of few examples.

Tips to Use Wildcard Characters in Google Sheets Functions

We can learn how to use the above three wildcard characters in Google Sheets functions, not with search command, one by one.

I’m using basic forms of two Google Sheets functions in the examples to make you understand the use of wildcard Characters in Google Sheets functions. I am going to use Sumif and Sumifs functions in the examples below.

You can follow the same procedures to apply wildcards in other functions like Dsum, Sumproduct, count functions. etc. The procedure is same. So I am not repeating the steps with each and every functions here.

Note: If you want to learn the advanced use of functions; either switch to corresponding pages on this site or you can purchase our relevant eBooks from Amazon. See our downloads section for more details.

Wildcard Characters in Google Sheets Functions - Sample Data

We can use the above sample data.


SUMIF

Use of Wildcard Character ? (Question Mark) with Sumif

This wildcard character can represent or take the place of any single character.

“=sumif(C2:C11,”TV 2202″,G2:G11)” – This formula to sum “amount” if “product” is “TV 2202”. No wildcard character in use here.

“=sumif(C2:C11,”TV 220?”,G2:G11)” – This formula will sum “amount” if “product” is TV2202 or TV 2203. The last character is not important here. But there should be one character followed by TC 220. That’s important.

“=sumif(C2:C11,”TV 22??”,G2:G11)” –  Here the text can be “TV 2201”, “TV 2202” like any text. What important here is there should be two characters followed by “TV 22”.

Use of Wildcard Character * (Asterisk) with Sumif

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

“=sumif(C2:C11,”TV*”,G2:G11)” – This formula will sum “amount” if “product” is TV2202, TV 2203456 or any word starting with “TV”.

“=sumif(C2:C11,”TV 2*3″,G2:G11)” – Here it can be any text but should start with “TV 2” and end with “3”. That means “TV 244444443”, “TV 2111113” etc.

Use of Wildcard Character ~ (Tilda) with Sumif

The use of this wildcard character is entirely different compared to the above two and also little bit confusing.

Sometimes you may have text with ? or * as normal characters. So we can tell Google Sheets that what following after ~ is a normal character. For example ~*. In this the * is a normal character not wildchard character.

“=sumif(B2:B11,”No~?th”,G2:G11)” – Here the formula is checking for the text “No?th”. The tilde wildcard in the formula is to tell the function that the question mark in the text is a normal character.


SUMIFS

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″)” – No wildcard characters used. This formula is to sum “amount” if the “area” is “north” and “product” is “TV 2202”.

“=sumifs(G2:G11,B2:B11,”North”,C2:C11,”TV 22??”)” – Here wildcard characters used. This formula is to sum “amount” if the “area” is “north” and “product” is “TV 2202” or any text but the starting characters should be “TV 22” and followed by two more characters.

Use of Wildcard Character * (Asterisk) with Sumifs

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

“=sumifs(G2:G11,B2:B11,”*East”,C2:C11,”TV*”)” – This Sumifs formula sums “amount” if “area” is either “North East” or “South East” and “product” is any text starting with”TV”

Use of Wildcard Character ~ (Tilda) with Sumifs

Please refer above Sumif section Tilda use tips.


The above are the examples to the use of wildcard characters in Google Sheets. Also this is answer to your similar questions like;

1. How to use wildcard characters in Dsum function in Google Sheets?

2. How to use wildcard characters in Sumproduct function in Google Sheets?

3. How to use wildcard characters in count functions in Google Sheets?

or

4. How to use wildcard characters in any functions in Google Sheets

The above examples are enough to learn how to use wildcards in any Google Sheets functions as the procedures are same. Hope you find this tutorial useful. If yes please promote us by sharing on social networks.

LEAVE A REPLY

Please enter your comment!
Please enter your name here