Find and Replace Multiple Values in Google Sheets

In this tutorial, I will show you how to find and replace multiple values in Google Sheets.

Having an extensive database with thousands of rows is all well and good until you see that you made a mistake with spelling or an acronym. Everything seems terrible when you realize that you have to correct them all.

The great news is that you don’t have to do it all manually. As there are a variety of ways to find and replace multiple values in Google Sheets that can save you in your current predicament.

How to Find and Replace Multiple Values in Google Sheets

1. Select your range (skip this if you’ll be using the whole spreadsheet)
2. Hit CTRL + H (Mac: CMD + SHIFT + H) to open Find and Replace
3. Type the search word in the Find field
4. Enter the replacement word in the Replace with field
5. Hit Replace all and Done

Find and Replace Multiple Values in Google Sheets Video Tutorial

3 Ways to Find and Replace Multiple Values in Google Sheets

The most common way to do this is by using the native feature to find and replace multiple values in Google Sheets.

Alternatively depending on the scenario, the SUBSTITUTE and REGEXREPLACE Functions may work well as well.

Find and Replace

The Find and Replace feature of Google Sheets can be accessed by using the keyboard shortcut CTRL + H (Mac: CMD + SHIFT + H). Another way to open it is by going to the ‘Edit’ menu and selecting the ‘Find and replace’ option.

To start, I need to select the range where I want to find and replace multiple values in Google Sheets. If I need to run the find and replace across the entire spreadsheet, I’ll just skip the first step. And then open the ‘Find and replace’ option.

Once the ‘Find and replace’ window is open, I just need to identify the word to be searched for and its replacement. Next, I need to press the ‘Replace all’ button.

A confirmation window will appear. To proceed, I just need to click on OK.

Caption: Replace all values command confirmation window

Once done, a confirmation message will appear near the bottom of the ‘Find and replace’ window.

All instances of the searched word are now replaced with the ‘Replace with’ value. To finish, click on Done.

SUBSTITUTE Function

The thing about using the native feature to find and replace multiple values in Google Sheets is that it replaces the original values. If you don’t want that to happen, then your best bet is the SUBSTITUTE Function.

Basically, it’ll still find a piece of text in a string and will replace it with the value that you nominated. But this time, it’ll place the updated string in the cell where your formula is in while the original string is untouched, as long as you use a cell reference.

The syntax of SUBSTITUTE is

=SUBSTITUTE(text_to_search, search_for, replace_with)

Where

• text_to_search – the original string
• search_for – the piece of text in the original string
• replace_with – the value to replace the search_for parameter

To do this, just have a column which will contain the original text. If you type in the formula at the topmost cell of another column, Google Sheets will prompt you with a suggested autofill once you hit enter.

If you click on it, the formula will be appropriately applied to the rest of your dataset.

Another alternative is to manually copy the formula by using the fill handle and dragging it across the range where you want it to be copied. This is useful if you want to control where the copied formula will be pasted.

Lastly, if the parameters for all the values in a column are relatively the same, you may use the ARRAYFORMULA Function to enclose your SUBSTITUTE Function while you adjust the text_to_search to be a range that covers the entire column instead of a single cell.

Now, if you are not sure about the search_for parameter of the SUBSTITUTE Function, there may be other ways for you to conduct your search with the next method.

REGEXREPLACE Function

The REGEXREPLACE Function is similar to SUBSTITUTE in that it can also find and replace multiple values in Google Sheets. That said, the former can be a bit more flexible in terms of how the search is done.

This is because REGEXREPLACE uses regular expressions to do the search.

The syntax of REGEXREPLACE is

=REGEXREPLACE(text, regular_expression, replacement)

Where

• text – the original string
• regular_expression – the part of the original string to be searched
• replacement – the value to replace the results of the regular_expression parameter

For the regular_expression parameter, take note that Google products use RE2 for regular expressions.

For our example below, I’m using the same search term as before (“P.O.”).

Using the suggested autofill of Google Sheets is a good idea to speed up the process. 

Alternatively, you may also manually adjust the copying of the formula only to the cells that you wish the formula to be in. This is done by clicking and dragging the formula cell’s fill handle (lower right corner of the cell).

Lastly, similar to the SUBSTITUTE Function, I used the ARRAYFORMULA Function to enclose the formula with REGEXREPLACE.

Knowing the different ways to find and replace multiple values in Google Sheets is extremely valuable in the long run. Mistakes are always bound to happen and knowing how to fix these mistakes is important.

Frequently Asked Questions about How to Find and Replace Multiple Values in Google Sheets

Conclusion on How to Find and Replace Multiple Values in Google Sheets

You can find and replace multiple values in Google Sheets by using the ‘Find and Replace’ feature. To start, select the range of the target values, then hit CTRL + H (Mac: CMD+SHIFT+H) to open ‘Find and Replace’. Next, type the search word in the ‘Find field’ and enter the replacement word in the ‘Replace with’ field. Lastly, hit ‘Replace all’ and ‘Done’.