3 Methods to find and replace content in Google Sheets:
- Use Find and Replace from the menu
- Use the SUBSTITUTE formula
- Use the REGEXREPLACE formula
Let us begin by looking at the methods in detail.
Method #1: Use Find and Replace from the Menu
This is the standard way to find and replace text or numbers in Google Sheets.
Step #1: Open your Google Sheets spreadsheet
On the Google Sheets site, click on the white box with the colorful cross, above Blank, to open a new blank spreadsheet.
If you already have an existing spreadsheet, open that.
Step #2: Open the Find and Replace window
Click on Edit in the main menu bar.
A dropdown menu will appear.
Click on Find and replace at the bottom.
You can also use the keyboard shortcut, by pressing Ctrl + H in Windows or ⌘ + Shift + H if you are using a Mac.
The Find and replace window will open.
Step #3: Enter the desired parameters
Type in the text and/or numbers you want to find, in the box next to Find.
In the input box next to Replace with, type in the new text and/or numbers to replace the old ones.
Under the Search dropdown, you can select different options. These are used to perform the operation on the whole document, an individual sheet, or a range of cells.
You can use features like the Match case, Also search within the formula as needed.
For our example, we will replace the text ‘Google Sheets’ with ‘not MS Excel’
After typing in the desired text/numbers, click on the Replace All button.
You can also click on Replace. This will replace only the first instance of the text you are looking for.
When you click the button, the window will close and you will see that the replacements have been done.
Method #2: Use the SUBSTITUTE Formula
This method can be useful when you don’t want or can’t change the original data. Here, you’ll take the original data as a source and see the replaced result in a different cell(s).
Step #1: Open your Google Sheets spreadsheet
On the Google Sheets site, click on the white box with the colorful cross, above Blank, to open a new blank spreadsheet.
If you already have an existing spreadsheet, open that.
Step #2: Type the SUBSTITUTE formula
Select a cell by clicking on it.
In the cell, type the following formula:
=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
The meaning of the arguments is given below:
- text_to_search: This is where you want to search. It can be a singular cell, or a range.
- search_for: This is the content you are looking for.
- replace_with: This is the replacement content.
- [occurrence_number]: Use this if you want the replacement to be done only a limited number of times. Usually, all occurrences of the content you are looking for will be replaced. If [occurrence_number] is specified, you can limit the number of replacements being made.
In our example:
- text_to_search: This would be cell A1. (You can also type in the desired string in the formula itself if you don’t have any cell(s) to provide reference.)
- search_for: ‘Google Sheets’. These are the words we are looking for. Note that the input should be enclosed by double quotes.
- replace_with: ‘not MS Excel’. These are the replacement words. Note that the input should be enclosed by double quotes.
- [occurrence_number]: 1 (It can be left blank as well)
ob-image-box-shadow
Step #3: Press Enter
After completing the formula, press Enter on your keyboard. You will see that the content has been replaced as per our requirements.
Method #3: Use the REGEXREPLACE Formula
This method is similar to the SUBSTITUTE formula. The only difference is that the REGEXREPLACE formula works only with text.
Step #1: Open your Google Sheets spreadsheet
On the Google Sheets site, click on the white box with the colourful cross, above Blank, to open a new blank spreadsheet.
If you already have an existing spreadsheet, open that.
Step #2: Type the REGEXREPLACE function
Select a cell by clicking on it.
In the cell, type the following formula:
=REGEXREPLACE(text, regular_expression, replacement).
The meaning of the arguments is given below:
- text: This is where you want to search. It can be a singular cell, or a range.
- regular_expression: This is the content you are looking for.
- replacement: This is the replacement text.
In our example,
- text: This would be cell A1. (You can also type in the desired string in the formula itself if you don’t have any cell(s) to provide reference.)
- regular_expression: ‘Google Sheets’. These are the words we are looking for. Note that the input should be enclosed by double quotes.
- replacement: ‘not MS Excel’. These are the replacement words. Note that the input should be enclosed by double quotes.
Step #3: Press Enter
After completing the formula, press Enter on your keyboard. You can see that the operation is complete as per our requirements.
Conclusion
These three methods must have given you a basic idea of the Find and replace operation in Google Sheets.
Keep in mind that the SUBSTITUTE and REGEXREPLACE formulas are limited to one replacement at a time. In contrast, the Find and replace option has a much wider application.