In this tutorial, you will learn how to find and replace in Google Sheets. When you’re working in Google Sheets, you may find that you need to change key pieces of information across the entire spreadsheet. For example, you might need to update information that has changed since the spreadsheet was created, or correct errors. Fortunately, Google Sheets
makes it quick and easy to find specific text wherever it occurs in a spreadsheet and replace it with different text. Read on to learn how. Open the Edit menu and click on the Find and Replace option, or use the keyboard shortcut:Step 1
Step 2
In the Find and Replace dialog that appears, type the text you want to replace in the Find field, and the text you want to replace it with in the Replace With field. The screenshot below shows what this looks like when finding all instances of the name “John Smith” and replacing it with “Jane Doe”
Step 3
Choose where you want to perform the find and replace from the Search dropdown. You can choose to search the entire spreadsheet, the currently active sheet, or a specific range
Step 4
If you selected Specific Range in Step 3, a field will appear where you can enter the range to search
Step 5
If you would like to match the exact case of the text you enter, Match only entire cell contents, or search within formulas not only values, check corresponding checkboxes.
Step 6
You can also use regular expressions to search for text that matches a pattern, such as an ID or email address. Regular expressions are a full topic unto themselves and beyond the scope of this tutorial but there’s a lot of information on the internet on how to use them
Step 7
Use the Find button to step through all instances of the search text, and the Replace button to replace that text
Step 8
You can also click Replace All to replace all instances of the search text with one click
Step 9
Click the green Done button when you’re done to close the Find and Replace dialog
Summary
- Summary
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to find and replace in Google Sheets. Want more? Check out all the Google Sheets Tutorials.
You might also like:
How do you use REPLACE in Google Sheets?
To use REPLACE in Google Sheets, you simply need to type =REPLACE( into the cell where you want to perform the replacement, and then input the text you want to replace, the text you want to replace it with, and the number of times you want it to occur. For example, if you wanted to replace all instances of "apple" with "orange" in the text "I love apples" you would type =REPLACE("I love apples","apple","orange",3) into the cell. This would replace the first, third, and fifth instances of "apple" with "orange".
What is the syntax of REPLACE in Google Sheets?
The REPLACE() function in Google Sheets replaces a substring in a text string with another substring. The syntax for the REPLACE() function is as follows:
=REPLACE(text, old_substring, new_substring)
In the function, text is the text string in which the substring is to be replaced. old_substring is the substring that is to be replaced with new_substring.
What is an example of how to use REPLACE in Google Sheets?
In Google Sheets, the REPLACE function can be used to replace text in a cell with another text string. The function takes two arguments: the text to be replaced and the text to replace it with. The function can be used to replace text in a cell with a new text string, or to replace specific characters in a text string with another character.
When should you not use REPLACE in Google Sheets?
There are a few occasions when you should not use the REPLACE function in Google Sheets. One instance is when you are trying to replace text that is not in a specific location. For example, if you want to replace the word "the" with "a" throughout a document, you would use the find and replace feature in a word processing program, rather than the REPLACE function in Google Sheets. Another time you should not use the REPLACE function is when you are trying to replace text that is formatted in a specific way. For example, if you want to replace all the text in a cell that is bolded, you would use the find and replace feature in Google Sheets, rather than the REPLACE function.
What are some similar formulae to REPLACE in Google Sheets?
Some similar formulae to REPLACE in Google Sheets are:
=REPLACE(text, start_num, num_chars, new_text)
=REPLACE(text, find, replace, text)
=REPLACE(text, from_text, to_text)
=REPLACE(text, pattern, replacement)
=REPLACE(text, old_text, new_text)