Find and Replace Google Sheets [Ultimate Guide]

In this tutorial, we will learn how to find and replace text in Google Sheets.

In large spreadsheets, replacing multiple entries can be arduous and time consuming. Replacing strings can also be necessary if a certain entry or entries are typed incorrectly. Nobody, not even an intern, wants to manually search through thousands of entries.

In several applications such as Word and Notepad, the replace function is partnered with their find function. Luckily, the same is true to Google Sheets. The find and replace tool combines the two parts of the process into one simple method.

Find and Replace vs. Google Sheets Search

Find and replace is a more complex feature than the Google Sheets Search tool, which can be helpful in certain instances but unnecessarily complex for many simple tasks.

‍

Search Pros

If you merely need to find data in the a spreadsheet, the search feature is quick, simple, and easy. There is no need to replace text, you merely enter your search term and press enter to cycle through the results

‍

Find and Replace Pros

On the other hand, the find and replace tool offers a lot more customization if you need to edit the spreadsheet. Not only can you make multiple edits quickly to your data, but the tool also has many different options to make your searches more powerful, specific, and efficient.

For example, the find and replace feature offers these extra options that the search tool does not:

• match case
• match entire cell
• regular expressions
• search within formulas
• search for similar expressions

‍

How to Find and Replace in Google Sheets

Here are the steps:

‍

1. From the main menu, select Edit, then Find and Replace.

You can also use the keyboard shortcut Ctrl + H (Cmd + H on Mac) to find and replace.

This will load the Find and replace feature:

2. Type the word or string that you want to find into the "Find" textbox.

‍

3. Type the replace text string in the "Replace with" textbox.

‍

Type the word or string you want to replace in the text box beside the Find label and the string to replace it in the text box beside the Replace with label. For our example, we want to replace Zurich with Barnard.

There are additional options. We will discuss two of them:

• By default, the case of the characters of the string is ignored. Ticking the Match case option means the case of the characters must also match.
• By default, Google Sheets will highlight the cell that contains the string, whether it is the only string inside the cell or not. Ticking the Match entire cell contents box will make Google Sheets highlight the cells that contain only the specified string.

‍

You can use these options to narrow down the initial search.

‍

Another way to limit your search is through the search parameters. By default, the tool will search the entire spreadsheet. You can choose to search a specific sheet or even a specific range, both of which narrows the parameters.

4. Click Replace all.

‍

Depending on what you need, you can replace every instance at once or replace the strings one-by-one.

‍

To replace the string one-by-one, click Find first. Google Sheets will highlight the first result. The Replace button will become visible. You can enter a specific string to replace the original.

Click the Replace button. A message saying that the string was replaced will be displayed. Google Sheets will automatically go to the next instance of your search term. From there, you can click Replace again or enter a new term in the Replace with textbox and then press replace.

If you want to replace them all at once, click the Replace all button. A message indicating the number of instances replaced will appear after the operation.

‍

Before:

After (note the two upper cells containing “Zurich” now contain "Barnard"):

Find and Replace FAQs

Replacing Entire Cell contents

‍

By selecting the option to match the entire cell contents, Google Sheets will make sure that the entire cell matches with your search term.

‍

This is especially helpful if you need to search names that could have a longer version, such as "Dan" and "Daniel."

Search Case Sensitive

‍

Another option allows you to match the case of your search term, which can help avoid finding strings in the middle of a word.

‍

For example, searching for "Pan" and selecting this option will not return a result for the word "Japan" because the "p" is not capitalized.

Searching Without Replace Feature

‍

Check out this article to find a easier Google Sheets search method if you merely want to find a certain string rather than find and replace.

‍

Search in Formulas

‍

Finally, the find and replace dialog box allows you to search within formulas by checking the corresponding box.

‍

This feature can be especially useful if you accidentally miswrote a formula and then copied it down a column.