In this tutorial, you will learn how to find and replace wildcard in Google Sheets.
How To Find And Replace Wildcard in Google Sheets
Quick Navigation
In Google Sheets, wildcards are unique characters or symbols that can be used to narrow down your queries. These symbols represents characters or strings within a function. There are three different wildcard characters available in Google Sheets:
1. An asterisk (*) can stand in for any number of characters. ‘Bill*’, for instance, can refer to Bill, Bills, Billy, Billiards, etc.
2. Question Mark (?): Only one character is represented by this wildcard. B?ll, for instance, can stand for Ball, Bell, Bill, etc.
3. Tilde (~): It is customary to use this wildcard symbol before the * or ? wildcards. It instructs Google Sheets to treat the following character as a regular character symbol rather than a wildcard. For instance, the search term “b~*” means to look for all strings that contain the exact word “b*.”
Find and Replace Wildcard
Unfortunately, Google Sheet’s Find and Replace dialogue box does not support fine-tuning searches utilising wildcards. Simply put, Find and Replace will disregard the fact that a wildcard is present and treat it like any other character when doing a search. Additionally, there are no options to check for wildcards in the dialogue box.
To hone searches in this area, Find and Replace does support Regular Expressions. It is also safe to claim that regular expressions allow us to further tailor our searches than what is possible with wildcards.
Regular Expressions
A series of symbols or characters called a “regular expression” is what we use to represent other characters and strings. Metacharacters is another name for the symbols used in regular expressions.
Find and Replace Regular Expressions
Now that we are familiar with regular expressions and what they can accomplish, let’s apply regular expressions to find and replace the student IDs with ‘F’.
Step 1
First, you need to have a clean and tidy group of data to work with. Then, select the range of data you want to find and replace from.
Step 2
We will then open the Find and Replace dialog box (Keyboard shortcut: CTRL+H). We will search for ‘^F’. The ‘^’ is a regular expression that searched for any cells that start with the letter F. Then, we will select ‘Match case’ and ‘Search using regular expressions’ to make this work.
Step 3
Once you press ‘Find’ the select range will turn to green outlined and only the cells that matches the criteria will turn blue.
Summary
That’s all there is to it. You are welcome to copy the example spreadsheet below to see how it is done. The most crucial lesson is to enjoy yourself while doing it.
In this tutorial, I covered how to find and replace wildcards in Google Sheets. Want more?
Check out all the Google Sheets Tutorials.