In this article:

Find and Replace Google Sheets [Ultimate Guide]

May 8, 2024

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.

find and replace in google sheets

This will load the Find and replace feature:

Find and replace box.

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.

Find and replace box. The string “Zurich” will be replaced 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.

A search result highlighted.

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.

One instance of “Zurich” replaced with “Barnard”. Message says “Replaced ‘Zurich’ with ‘Barnard’”.

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:

google spreadsheet find and replace

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

Instances of “Zurich” at E1 and G1 replaced by “Barnard” at the same time after clicking the “Replace all” button.

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."

match entire cell contents

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.

match case

Searching Without Replace Feature

Check out this article to find an 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.

We hope this article has helped you and given you a better understanding of how to find and replace in Google Sheets. You might also like our articles on how to share only one sheet in Google Sheets and how to make Google Sheets dark mode.

To optimize your workflow, we recommend reading our guide on how to create mail merge labels in Google Sheets.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->