The 3 Best Ways to Find and Replace in Google Sheets

Looking to easily find and replace text strings in Google Sheets? Here are three simple ways to do so.

Large data sets can change dramatically over time. Many of us are responsible for identifying this data and updating old values with new ones. This may be simple enough when only a few data rows exist. However, this can become very tedious with larger data sets.

Thankfully, Google Sheets has several features that effortlessly allow you to do this. This guide will look at some of the best ways you can find and replace in Google Sheets. Read on to master them all.

3 Ways to Find and Replace in Google Sheets

There are three ways to look for a specific string and replace it in Google Sheets. They are:

• The Find and replace tool
• The SUBSTITUTE function
• The REGEXREPLACE function

Let's have a look at each in-depth, so you can decide which one works best for your spreadsheets.

Finding the Built-in Find and Replace Tool

Thankfully, Google has made it extremely easy to find and replace text using just a few simple steps. You can do it with the simple use Find and replace tool, which is just as simple to use as the Excel find and replace tool. But, before we look at how you can use this tool, let's cover how you can access it in your Google Sheets.

To open the Find and replace tool:

1. Click on Edit in the top bar.
2. Click on Find and replace in the dropdown menu. This will open a new window in the middle of the screen.

Alternately, you can use a keyboard shortcut to open the tool.

• On Windows, the shortcut is Ctrl+ H
• On macOS, the shortcut is Command+ Shift+ H

1. Using the Find and Replace Tool in Google Sheets

To demonstrate the tool, let's use a sample spreadsheet as an example. The main goal here is to search for the word "desk" and replace it with the word "table." Here are the steps you need to follow to perform a search and replace in Google Sheets:

1. In the Find and replace window, enter the word you want to search for in the Find textbook. In this case, it is the word "desk."
2. You can then simply click on the Find button at the bottom of the window to search for the term in your spreadsheet.
3. This will select the cell containing the specified word.
4. If you wish to replace the specified word, type in the new word in the Replace with field. In this case, the word is "Table."

There are two ways in which you can replace the word.

• You can click on the Replace all button to replace every instance of the word with the new one.
• If you wish to replace the values one by one, click on Find to look for the values in the table. This will cycle through each value in the spreadsheet one by one. Once you find the value you wish to replace, click on the Replace button.

As you can see, multiple options with checkboxes beside them allow you to perform several actions.

• Match case allows you to look for words with the exact capitalization.
• The Also search within formulas and Also search within links options search for the word within formulas and links, respectively.

2. Using the SUBSTITUTE Function to Find and Replace

In Google Sheets, SUBSTITUTE can replace the text in cells with other strings. There are three parameters for the function. The function will replace all instances of the text in the first parameter's cell with the text in the second parameter's cell.

Here is the syntax for the SUBSTITUTE function in Sheets:

=SUBSTITUTE(searchtext, searchfor, replacewith, occurrences)

Here are the parameters used in this formula:

• searchtext: is the text in which we perform the search and replace function.
• searchfor: is the string we aim to search for in the searchtext parameter.
• replacewith: is the text that will replace the text in the searchfor parameter.
• occurrences: is an optional parameter that defines the instances for the searchfor text to be replaced within the spreadsheet.

Here are the steps you need to follow to use the SUBSTITUTE formula in Sheets:

1. Click on the cell where you wish to input the function.
2. Type in the initial part of the formula, which is =SUBSTITUTE(.
3. Now, type in the first parameter, which defines the cell range to look in. In this case, the cell range is B2:B31.
4. Add a comma to separate the parameters.
5. Now type in the word you are searching for. In this case, we write "Desk," including the quotation marks.
6. Add another comma.
7. Now add the third parameter, the word you wish to replace the text with. In this case, the word is "Office Desk."
8. Add a closing bracket to finish off the formula.
9. Press Enter to execute the formula.

You can also use the SUBSTITUTE function in Excel.

3. Using the REGEXREPLACE Function to Find and Replace

Along with REGEXEXTRACT and REGEXMATCH, REGEXREPLACE is one of Google Sheet's three regex functions. Regular expressions replace a part of a text string with a different text string. Regular expressions are complicated character sequences or search patterns that allow you to find certain patterns in a string and are even a part of basic Python programming.

With the REGEXREPLACE function, the strings can be replaced with any other text after the function detects them. Here is the syntax for the REGEXREPLACE function in Sheets:

=REGEXREPLACE(text, expression, replacement)

Here are the parameters used in this formula:

• text: this is a part of the text to be replaced.
• expression: this defines the regular expression in which all the matched instances will be replaced.
• replacement: this is the text which will replace the text.

Here are the steps you need to follow to use the REGEXREPLACE formula in Sheets:

1. Click on the cell where you wish to input the function.
2. Type in the initial part of the formula, which is =REGEXREPLACE(
3. Now, type in the first parameter, which defines the cell range to look in. In this case, the cell range is B2:B31.
4. Add a comma to separate the parameters.
5. Now type in the word you are searching for. In this case, we write "Desk," including the quotation marks.
6. Add another comma.
7. Now add the third parameter, the word you wish to replace the text with. In this case, the word is "Table."
8. Add a closing bracket to finish off the formula.
9. Press Enter to execute the formula.

Pick the Right Replacement

Each method to find and replace in Google Sheets functions better under certain situations. For example, REGEXREPLACE is best for replacing part of a text string, while the Find and replace tool is the simplest for basic uses.

Familiarizing yourself with each of these methods will help you on your way to being a Google Sheets pro.