Mehrab Imtiaz
Find and Replace is an indispensable tool provided by Google Sheets. If you are thinking of the simple Find option, then you couldn’t be further away from the truth.
The Find and Replace option in Google Sheets caters to all levels of spreadsheet users, incorporating regular expressions and formulas.
On that note, let’s see what Find and Replace has on offer for us and understand the many ways we can utilize it for our spreadsheets.
Why do we need Find and Replace?
Google Sheets’ Find and Replace function looks through the entire worksheet for your specific word and replaces it with any value (even blanks).
The Find and Replace dialog box offers more than just finding and replacing. It presents us with multiple criteria for our searches, even some niche or complex ones like finding with regular expressions and within formulas.
To list some of the conveniences down:
- You can make case-sensitive searches.
- Helps look for and within formulas.
- You can search with regular expressions for strings that follow a pattern.
- You can use wildcards to enhance your searches.
Read More: Find and Replace with Wildcard in Google Sheets
3 Ways to Find and Replace in Google Sheets
1. Find and Replace In a Browser
We have created this small worksheet to show you the various uses and aspects of the Find and Replace function of Google Sheets. We are running it on a Chrome-based web browser.
Before we dive into the Find and Replace option, if your worksheet/dataset is small and simple, it is always better to use the Find option to get your works done.
Keyboard Shortcut: CTRL+F (for PC) or CMD+F (for Mac)
We are looking for the word “shirt”. As you can see, it highlights all the instances of the word “shirt”. You can replace the ones that you choose to manually.
But for a more in-depth search and replace option we will utilize the Find and Replace function of Google Sheets.
The natural way to get to the Find and Replace dialog box is by navigating through the Edit tab from the Toolbar menu.
Edit > Find and Replace
Or you can simply use the keyboard shortcuts:
- For PC: CTRL+H
- For Mac: CMD+SHIFT+H
Read More: How to Use Find and Replace in Column in Google Sheets
a. All Instances
As we have just seen with Find, we will be trying to replace the word “shirt” with something else, like “pant”. Let’s see how it goes.
As we click on Find, the function will cycle through every instance of “shirt” in the worksheet with every click. Once it is done cycling, it will notify you that the loop is complete.
At any point during this cycle, you can click on the Replace button to replace the instance with your desired word, “pant”.
But if you want to replace all of the instances, simply click the Replace all button.
The Replace all result:
Note: By default, All Sheets will be selected in the Find and Replace dialog box. You can change this option anytime.
b. Match Case
From this point onward, we will be using the conditional options available to us in the Find and Replace dialog box, starting with the Match case option.
These are the conditions we have applied:
We will be replacing all instances of “shirt” with “pant”, but it will be case-sensitive as determined by checking the Match case option. Click Replace all.
Our result:
As you can see, the two instances of “Shirts” have not been replaced since the “S” is in caps.
c. Match Entire Cell Contents
The next option we will be looking at is the Match entire cell contents option. Checking this option will tell the Find and Replace option to make a character-for-character match in an individual cell.
Let’s show an example:
Similar to our previous example, we will try to find the word “shirt” in our worksheet and replace it with “Redacted”, and this time, we will check the Match entire cell contents option. Let’s see what happens:
The result:
It shows No matches found because none of our cells contain just the word “shirt” individually in a cell.
Now, if we change the Find word to “shirts” however:
Read More: Find All Cells With Value in Google Sheets (An Easy Guide)
Similar Readings
- How to Find Correlation Coefficient in Google Sheets
- Replace Space with Dash in Google Sheets (2 Ways)
- How to Find and Delete in Google Sheets (An Easy Guide)
- Use FIND Function in Google Sheets (5 Useful Examples)
- How to Find Slope of Trendline in Google Sheets (4 Simple Ways)
d. Matching with Regular Expressions
A unique way to approach Find is by using Regular Expressions. These are special symbols, often known as “metacharacters” that have their own functionality within Google Sheets.
| Character | Meaning |
|---|---|
| ^ | The beginning of the string |
| $ | The end of the string |
| . | A single character |
| ? | Zero or one occurrence of a character or string |
| * | Zero or more occurrences of a character or string |
| + | One or more occurrences of a character or string |
| | | The Or operator |
| () | This holds a group of metacharacters inside it and represents that sequence of characters |
| [] | This holds a set of characters and represents any one of the characters inside it |
| [^] | This holds a set of characters and represents any one of the characters not listed inside it |
| \ | This is used to escape a special character |
With the help of these regular expressions, we can customize our Find searches down to match each character.
Let’s now look at an example where we will find and replace a string that starts with a “Tr” and ends with an “s”. We will keep our search case-sensitive and will only be implemented in the Shipment column.
Our regular expression for this condition will be:
^Tr[a-zA-Z ]+s$Formula Breakdown
- ^Tr: We look for the characters “Tr” at the beginning of the string
- [a-zA-Z ]+: All alphabetical characters, both small and caps, are included
- s$: Our string must end with an “s”
Make sure the Search using regular expressions option is checked. Otherwise, the formula will not work.
Our result:
Read More: How to Search in Google Spreadsheet (5 Easy Ways)
e. Search Within Formula
For our final example, we will be searching for words within formulas.
Our Special ID column contains a very simple formula:
=B3&"XP"
To show the example, we will find the string “XP” and replace it with “REP”. Our range will be the Special ID column. And make sure to check the Also search within formulas option.
Our result:
Read More: How to Find the Range in Google Sheets (with Quick Steps)
2. Find and Replace In a Mobile Device
You can also use the Find and Replace function in Google Sheets’ mobile application, for both Android and Apple devices. Simply follow these steps:
Step 1: On the top-right corner of the screen, you will see an icon with 3 dots. Tap and hold to bring up the menu.
Step 2: The Find and Replace option should be at the top of the menu. Tap on it.
Step 3: As per usual, type in your search (at the top of the screen) and what to replace it with (at the bottom of the screen).
And you are done!
As you may have noticed, the mobile version of the Find and Replace function is not as elaborate as the browser one. So if you have to make replacements based on complex conditions, the browser is the way to go.
3. Formula to Find and Replace in Another Cell
If you do not want to replace strings in a cell as a whole, Google Sheets provides us with a couple of functions to take the string from one cell and replace it with whatever modifications we desire and put it in another cell. These are:
- SUBSTITUTE
- REGEXREPLACE
We will be using the following simple table to show the examples.
a. SUBSTITUTE Function
This is a simple substitute function that takes a cell reference, looks for a designated string and replaces that string with a user-defined string. It is case-sensitive.
The SUBSTITUTE function syntax:
SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
Our example is to find the string “XP” and replace it with “REP” In the range.
Our formula:
=SUBSTITUTE(C3,"XP","REP")
Read More: Find Value in a Range in Google Sheets (3 Easy Ways)
b. REGEXREPLACE Function
This function follows the same fundamentals as SUBSTITUTE, only this time we have to build a regular expression for our condition.
The REGEXREPLACE function syntax:
REGEXREPLACE(text, regular_expression, replacement)
Our example conditions are the same: replace all strings that end with “XP” with “REP”.
Our formula:
=REGEXREPLACE(C3,"XP$","REP")
For our formulas, we have kept things simple. But know that much more complex conditions can be applied, especially with REGEXREPLACE.
Read More: Use REGEXREPLACE to Replace Multiple Values in Google Sheets (An Easy Guide)
Final Words
Congratulations, you have finally made it to the end of the article. Considering our topic of discussion, it does seem like a long ride. But from our journey, we have understood that Find and Replace in Google Sheets can be used at great depth and can match any user’s needs.
Please feel free to leave any queries or advice you might have in the comments below.
Related Articles
- How to Find Hidden Rows in Google Sheets (2 Simple Ways)
- Find Uncertainty of Slope in Google Sheets (3 Quick Steps)
- How to Find Median in Google Sheets (2 Easy Ways)
- Find Frequency in Google Sheets (2 Easy Methods)
- How to Find Edit History in Google Sheets (4 Simple Ways)
- Find Largest Value in Column in Google Sheets (7 Ways)
- How to Find Trash in Google Sheets (with Quick Steps)
