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 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.
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
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:
Here are the steps:
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:
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:
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.
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"):
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."
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.
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.
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.
