Searching in Google Sheets just got a whole lot easier with wildcards. Wildcards allow you to make flexible searches using special characters to match unknown parts of your search term. Whether you need to find cells containing certain texts, numbers, or dates, Google Sheets wildcards have got you covered.
In this article, we’ll explore what exactly Google Sheets wildcards are and why they are so useful when searching spreadsheets. Then, we’ll walk through some practical examples of how to leverage wildcards for tasks like finding cells starting with or containing specific text, numbers within a range, or dates before or after a certain date.
With just a grasp of the basics, you’ll be able to integrate Google Sheets wildcards into your regular spreadsheet work for quick and versatile searches.
As you become more familiar with the available wildcards in Google Sheets, you may be surprised just how much time and effort they can save you. Whether double-checking formula outputs across an entire sheet or gathering data from a table to summarize, wildcards take the headache out of hunting down matches in spreadsheets.
But before we get into the practical aspects of today’s guide, let’s quickly understand what Google Sheets wildcards are.
Understanding Google Sheets Wildcards
Wildcards may sound complicated, but they simply act as clever little symbols that make your searches super smart. When you use wildcards in Google Sheets properly, you unleash search powers you never thought possible – kind of like a spreadsheet superhero.
Before we show you how to maximize the power of wildcards to perform seamless searches in Google Sheets, here are some popular Google Sheets wildcards you should know.
The Asterisk (*)
The asterisk is the ultimate wildcard wonder, matching any number of characters in your search. Think of the asterisk as a flexible search machine, able to stretch out its extremities across rows and columns to discover matches no matter how the text, number, or date is displayed.
For example, if you search for “sales*2022”, the asterisk morphs into the perfect search sidekick, finding cells containing “sales target for 2022”, “sales data from 2022” and “2022 sales projections”.
The Question Mark (?)
Unlike the asterisk, the question mark stands for exactly one character in a search string. But don’t underestimate the ability of this unassuming wildcard to save you effort.
Searching for “data?2022” would match cells with “data for 2022”, “data in 2022” and “data by 2022”, but not “database 2022”. The question mark does the hard work of finding matches with only a one-character difference for you.
The Tilde (~)
The tilde wildcard has a special purpose – to escape the special meaning of other wildcard characters. Think of the tilde as a secret decoder or escape artist.
For example, searching for “(t~* )” would find only cells containing the exact text “t*.” The tilde ensures the asterisk is not treated as a wildcard itself but matched literally.
Now that you understand the real power of Google Sheets wildcards, let’s quickly see what you can do with wildcards in Google Sheets.
The Superpowers of Wildcards: 5 Astounding Uses
Wildcards may seem like tiny symbols, but they unlock tremendous timesaving abilities across Google Sheets. Here are 5 top ways to unleash wildcard superpowers:
Search Like a Mind Reader
The most common wildcard use is ultra-flexible searches. Include a wildcard in your search box query and find matches even if you don’t know the full cell text. It’s like a mind-reading trick for spreadsheets.
Filter Tables in a Flash
While filter toolbars let you show/hide data quickly, adding wildcards to filter conditions will instantly reveal only the rows you need. This makes it great for giant data sets or preparing reports.
Sum Numbers Magically
If you combine wildcards with SUMIF and other math formulas, you can seamlessly add up numbers matching a flexible condition. This lets you get totals for date ranges, partial text matches and more quickly.
Look Up Anything
When VLOOKUP struggles with messy data, adding a wildcard to your search key allows for partial or fuzzy matches. Simply put, wildcards make VLOOKUP way more powerful and usable.
Replace Without Repetition
Wildcard’s search and replace feature lets you change data quickly without worrying about missing variations. With wildcards, updating spreadsheets is now fast, easy, and error-proof.
Whether searching, filtering, math formulas, or lookups, a simple wildcard gives you an almost magical boost.
Copy Sample Sheet
If you want to follow along with today’s tutorial, feel free to copy our sample sheets. For those who have their own data, simply replicate the same steps we will show you.
Click Here to Copy Sample Sheet
Using Google Sheets Wildcards in a SUMIF Function
One extremely useful way to leverage wildcards is directly inside your SUMIF formulas. This allows flexible summing of numbers matching partial or uncertain text criteria. Let’s walk through it step-by-step.
For this example, we’ll use a modified sales tracker with data for January, including some missing or inconsistent data. Here is what it looks like:
Let’s say we need the total units sold for Alice only.
The usual SUMIF formula would miss some rows for Alice as the name format isn’t always consistent. But introducing a wildcard fixes this easily.
Let’s show you how to go about it.
Step 1: Choose A Blank Cell
Start by choosing a blank cell in your spreadsheet. This is where you want the total units sold by Alice to show up. For this example, we will go with cell E2. So, let’s go ahead and select that cell in our spreadsheet.
Step 2: Enter the SUMIF formula with Wildcard
Now that we have chosen a blank cell in our spreadsheet where we want the total units sold by Alice to appear, it’s time to input the SUMIF formula and watch the magic happen. Here is how to go about it.
Navigate to the formula bar and type in the following formula:
=SUMIF(B2:B6, “Alice*”, C2:C6)
Step 3: Press Enter
Having imputed the SUMIF formula along with a wildcard like we showed you in the previous step, you can now go ahead to press the Enter button on your keyboard. This action authorizes Google Sheets to generate the total unit sold for Alice in the selected cell.
If you did everything correctly, as we explained, you should have something like this:
Explanation of the Formula
The formula below, which we used in our first example:
=SUMIF(B2:B6,”Alice*”,C2:C6)
Breaks down into three key parts that work together:
- B2:B6 – This specifies the range of cells, from row 2 to row 6 in column B, that we want to evaluate against the criteria.
- “Alice*” – This sets the text criteria that will be used to identify matching cells. The asterisk (*) wildcard allows it to match cells containing “Alice” plus any additional characters.
- C2:C6 – Is the range to sum. It is where we’ll tally up values from column C only for the rows where column B matches the wildcard criteria.
So, for any cells within B2 and B6 that contain text with “Alice”, thanks to the asterisk, it will take the corresponding value in column C on that row and total it up.
Harnessing Google Sheets Wildcards to Filter and Find Data
Like supercharging SUMIF functions, you can also use wildcards to filter sheet data in powerful and flexible ways you never thought possible.
Let’s look at a step-by-step example.
We’ll use a modified inventory sheet of electronics products ready to be filtered. Here is what it looks like:
Our goal will be to quickly reveal Samsung products only. Normally, this would require applying an exact Samsung filter.
But by integrating a wildcard into our filter criteria, we handle variations like “Samsung TV” instead of just “Samsung.” This makes things much more flexible.
Here is how to go about it:
Step 1: Choose A Range to Filter
The first thing we want to do is select the range of data we would like to filter. For this example, we will go with A1:A5.
Step 2: Data > Create a Filter
After choosing the range where you want to apply the filter, navigate to the Data menu and select the option for Create a filter. Google Sheets should automatically add a filter icon to that column.
If you did everything right, you should see a filter icon on the selected range that looks like this:
Step 3: Tweak Filter criteria
Having added the filter icon to the column we want to filter using wildcard, it’s time to tweak the filter settings to meet our criteria. To do that, click on the filter icon for the column containing the product names.
From the options presented, select Filter by condition. Selecting this option will reveal a drop-down selection. From those options, select the option for Text Contains.
The video below provides better clarification on how to execute this step.
Now, in the filter criteria box, type your text, including wildcard. Since “Samsung” appears differently in rows 1 and 2, we’ll use Sams* to match. The * represents any additional characters.
With our wildcard filter entered, all we need to do is press OK to apply the filter. If you did everything right, your spreadsheet should be instantly filtered to display only rows for Samsung products.
Here is what our spreadsheet looks like after executing the last step:
Wildcard filters provide a fast way to slice and dice data without worrying about neat or standardized formats. Give them a shot next time you need to filter based on a loose keyword or text value match.
Using Wildcards with VLOOKUP (Partial Lookup)
VLOOKUP is an incredibly useful function for linking data across sheets and tables. But it breaks when your lookup value doesn’t match the source data perfectly. Thankfully, with Google Sheets wildcards, you can execute partial lookup seamlessly without any qualms.
Wildcard VLOOKUPs allow flexible partial matches between your lookup key and source table, making it way more powerful. Let’s walk through it:
Imagine we have a product table like this:
And on a separate cell, we need to lookup the cost for a “Galaxy S22” product using VLOOKUP.
Applying the usual VLOOKUP would fail since the names don’t exactly match. But using a wildcard lookup value makes it work despite typos or inconsistencies.
Here is how to go about it:
Step 1: Choose a Blank cell
First, choose a blank cell in your spreadsheet where you want the lookup cost to show up. If you look at our sample sheets, you’ll notice we already provided a column for that. So, for our example, we will go ahead and select cell E2.
Step 2: Input Vlookup Formula With Wildcard
Having selected the cell where you want the lookup cost to be, navigate to the formula bar and type in the following formula:
=VLOOKUP(“*” & D2 & “*”, A:B, 2, FALSE)
In case the data in your spreadsheet is different, just tweak the formula to reference the correct range.
Step 3: Hit Enter
After inputting the Vlookup formula with a wildcard as described in the above step, simply press Enter on your keyboard. Google Sheets should instantly generate the lookup result in the selected cell.
Here, check out what ours looks like:
And that’s it. With the wildcard lookup key added to our formula, VLOOKUP now gracefully handles typos, alternate phrases, and other imperfect data-matching scenarios. Making it way better than failing completely when you apply only Vlookup.
Give wildcard VLOOKUPs a try the next time you need flexible data matching across sheets and tables. It takes the traditional formula to a whole new level.
Final Thoughts
Google Sheets wildcards are an invaluable tool for simplifying and enhancing your spreadsheet work. They empower you to perform flexible searches, filter data effectively, calculate sums with ease, and improve VLOOKUP functionality. These wildcards, including the asterisk, question mark, and tilde, act as your trusty companions in the world of spreadsheets, allowing you to find and manipulate data more efficiently.
In today’s guide, we showed you different ways to fully maximize Google Sheets wildcards.
As you become proficient in using Google Sheets wildcards, you’ll discover how they can save you time and effort, whether you’re dealing with large datasets, performing complex calculations, or handling messy data.
By integrating wildcards into your spreadsheet workflow, you unlock a new level of productivity and precision.
In case you need more clarification on some of the steps detailed in today’s guide, feel free to leave a comment below, and we will be happy to respond.