The 3 Best Ways to Search and Replace in Google Sheets

Large datasets can change significantly over time. Many of us are responsible for identifying this data and updating the old values ​​with the new ones. This can be quite simple when there are only a few rows of data. However, this can become very tedious with larger datasets.

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


3 Ways to Search and Replace in Google Sheets

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

  • The Find and Replace tool
  • The REPLACE function
  • The REGEXREPLACE function

Let’s take a deep dive into each, so you can decide which is best for your spreadsheets.

Luckily, Google has made it super easy to find and replace text in just a few simple steps. You can do this with the easy-to-use find and replace tool, which is just as easy to use as Excel’s find and replace tool. But before seeing how you can use this tool, let’s see how you can access it in your Google spreadsheets.


To open the Find and Replace tool:

  1. Click Edit on the top bar.
  2. Click Find and Replace from the drop-down menu. This will open a new window in the middle of the screen.

You can also use a keyboard shortcut to open the tool.

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

To demonstrate the tool, let’s use a sample spreadsheet as an example. The main objective here is to search for the word “desk” and replace it with the word “table”. Here are the steps to perform search and replace in Google Sheets:

  1. In the Find and Replace window, enter the word you want to find in the To find textbook. In this case, it is the word “office”.
  2. You can then simply click on the To find 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 want to replace the specified word, type the new word in the Replace with field. In this case, the word is “Table”.


There are two ways to replace the word.

  • You can click the replace all button to replace each occurrence of the word with the new one.
  • If you want to replace values ​​one by one, click To find to find the values ​​in the array. This will cycle through each value in the worksheet one by one. Once you find the value you want to replace, click the Replace button.

As you can see, there are several options with checkboxes next to them that allow you to perform several actions.

  • Matching case allows you to search for words with the exact case.
  • The Also search in formulas and Also search in links the options search for the word in formulas and links, respectively.

2. Using the SUBSTITUTE function to find and replace

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

Here is the syntax of the REPLACE function in Sheets:

=SUBSTITUTE(searchtext, searchfor, replacewith, occurrences)

Here are the parameters used in this formula:

  • search text: is the text in which we run the find and replace function.
  • to research: is the string we are looking for in the searchtext parameter.
  • replace with: is the text that will replace the text in the searchfor parameter.
  • occurrences: is an optional parameter that defines the instances of the text search to replace in the worksheet.

Here are the steps to use the SUBSTITUTE formula in Sheets:

  1. Click the cell where you want to enter the function.
  2. Type the initial part of the formula, which is =REPLACEMENT(.
  3. Now type the first parameter, which defines the range of cells to watch. In this case, the cell range is B2:B31.
  4. Add a comma to separate parameters.
  5. Now type the word you are looking for. In this case, we write “Desk,” including quotation marks.
  6. Add another comma.
  7. Now add the third parameter, the word you want to replace the text with. In this case the word is “Desk.”
  8. Add a closing parenthesis to end the formula.
  9. Hurry Walk in to run the formula.


You can also use the REPLACE function in Excel.

3. Using the REGEXREPLACE function to find and replace

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

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

=REGEXREPLACE(text, expression, replacement)

Here are the parameters used in this formula:

  • text: it is a part of the text to be replaced.
  • expression: this defines the regular expression into which all matching instances will be replaced.
  • replacement: it is the text that will replace the text.

Here are the steps to use the REGEXREPLACE formula in Sheets:

  1. Click the cell where you want to enter the function.
  2. Type the initial part of the formula, which is =REGEXREPLACE(
  3. Now type the first parameter, which defines the range of cells to watch. In this case, the cell range is B2:B31.
  4. Add a comma to separate parameters.
  5. Now type the word you are looking for. In this case, we write “Desk,” including quotation marks.
  6. Add another comma.
  7. Now add the third parameter, the word you want to replace the text with. In this case the word is “Table.”
  8. Add a closing parenthesis to end the formula.
  9. Hurry Walk in to run the formula.

Choose the right replacement

Each search and replace method in Google Sheets works best in certain situations. For example, REGEXREPLACE is best for replacing part of a text string, while the Find and Replace tool is easiest for basic uses.

Familiarizing yourself with each of these methods will help you become a Google Sheets pro.

Comments are closed.