How to Use Wildcards in Google Sheets

Understanding how to use wildcard characters is an extremely useful skill that every spreadsheet user should develop.

Wildcard characters can be used in Google Sheets in combination with functions to do some very useful things.

In this tutorial, I will show you how to use wildcard characters in Google Sheets.

What are Wildcard Characters?

Wildcards are symbols that can be used to represent a variety of characters. They are often used in combination with conditional functions, search functions, lookup functions, and filtering to make your queries more dynamic when you want to return specific results.

In Google Sheets, there are three wildcard characters. The wildcards are an asterisk “*”, question mark “?”, and tilde “~”.

• Asterisk (*) – the asterisk is a wildcard that is used to represent any character and any number of characters
• Question Mark (?) – the question mark wildcard represents a single character
• Tilde (~) – the tilde in combination with the other wildcard characters (* and ?) to tell Google Sheets not to use wildcards. For example, if you are using “m~?” in your search this will let Google Sheets know that we want to search exactly for “m?” without using the question mark as a wildcard in this instance

To explain more clearly how these work. Let’s take a look at some example data

In the above example, I have a COUNTIF function set up to search for the value in column B, in the corresponding row in column A. The COUNTIF returns a 0 for every line, because there is no exact match.

We can use wildcards in this situation to make our search more flexible and return values that meet certain criteria.

Here is the same data, but this time set up with wildcards:

In this data, the COUNTIF returns a 1 in every line, because there is now a value that matches the search. Let’s take a look at what is happening here:

• =COUNTIF(A2,”*”&B2) – The formula in my second row uses the asterisk “*” wildcard. So this will search for my value in cell B2 with any character and any number of characters before my B2 value.
• =COUNTIF(A3,B3&”*”) – the formula in my third row also uses the asterisk “*” wildcard. This will search for my value in cell B3 with any character and any number of characters after my B2 value
• =COUNTIF(A4,B4&”?”) – the formula in my fourth row uses the question mark “?” wildcard. This formula will search for my value in cell B4 followed by any single character
• =COUNTIF(A5,”?”&B5) – the formula in my fifth row also uses the question mark “?” wildcard. This formula will search for my value in cell B5 with any single character before it
• =COUNTIF(A6,B6&”~?”) – the formula in my sixth row uses the tilde “~” wildcard. This tells Google Sheets not to use a wildcard search with the question mark used in the formula. So it returns a match because there is really a question mark that I am searching for.

Hopefully, by going through the above examples, you now have an understanding of how these wildcard characters are used, and why they are useful.

Now, let’s take a look at some more examples of common ways these might be used in your spreadsheet.

Filtering Data with Wildcards

One useful way of using wildcards in a spreadsheet is to use them to filter data to return values that match your criteria.

To show you  how to do this, I will be creating a filter using wildcards with this example data:

In this example data, I have different product SKUs and their corresponding sales. I will create a filter to only return values that start with a “T” in the first block and start with a “5” in the second numeric block of digits.

Here is how this is done:

1. First highlight the range of cells that contains that data that you want to filter

2. Next go into the top menu and select Data>Create a filter

3. You should now see that your data has a filter button in the top header row. Select the button in the top header row of the column you want to filter

4. Go to the Filter by condition option and select Text contains

5. Now we will be entering the format of our string that we want to filter for. Since I want to return any value that starts with a T in the first block, and a 5 in the second block, my filter is “t?-5*”. This will tell Google Sheets to return any value that starts with a T, followed by any character (the question mark wildcard), and then has a -5 followed by any character and any number of characters (the asterisk wildcard)

6. Select OK when you are done and you will see that your data has filtered according to the rules you specified in the previous step

 

Using Wildcards with the SUMIF Function

Another useful way to use wildcards is in combination with the SUMIF function to sum data that meets certain conditions.

For example, let’s say I have example data where I have the sales of various products like in the below image. I can use a SUMIF formula with a wildcard to sum the sales of a specific product type like socks.

Here is how this would be done in a spreadsheet:

The formula used in this example is:

=SUMIF(A2:A8,”*Socks”,B2:B8)

What this formula will do is look in my first range entered into the formula (A2:A8) and apply my conditional second argument “*Socks” to that range.

So since the asterisk wildcard means any character and any number of characters, “*Socks”, will pull out all the values that contain the word “Socks” with any values before it. The third argument (B2:B8) then tells Google Sheets to sum together those rows that were matching the “*Socks” condition.

That is why SUMIF wildcard is so useful. You can create conditions to only sum certain values that meet your requirements.

Partial Lookup with Wildcards

Another extremely useful way of using wildcards in a spreadsheet is using them in combination with a lookup function like VLOOKUP to do a partial lookup.

VLOOKUP by itself will only return values when it finds an exact match, but when you use wildcards with it, you can return partial matches, which will open up the possibilities on the different ways you can use this.

Here is an example:

In this example, I have a partial name in column C, and I want to do a VLOOKUP to return the full name, so I have setup a VLOOKUP with a wildcard to work even with a partial match.

My VLOOKUP formula is:

=VLOOKUP(C2&”*”,$A$2:$A$5,1,0)

What this will do is look for my value of C2 followed by any character and any number of characters. That is what the &”*” part of the formula does. It will look for a partial match inside range A2:A5 and return the first matching value.

Closing Thoughts

If you are a regular spreadsheet user, you absolutely need to understand how to use wildcards in your formulas. You can open up the possibilities and create formulas that will do a broader range of useful things.

We hope you now understand the value of using these in your own spreadsheets.

More Google Sheets Tutorials:
Count Number of Occurrences of a Value in a Column