For both simple and complex searches
When you need to find specific data in your spreadsheet and replace it, you can use Find and Replace in Google Sheets. What makes this feature more robust than a simple search is the advanced options.
You can find data that matches your letter case or the entire cell contents. You can also use regular expressions and search within formulas and links. Then, replace what you need with the click of a button.
Table of Contents
Stop struggling or searching manually through your large datasets. We’ll show you step-by-step how to use Find and Replace in Google Sheets.
Perform a Simple Search
You can perform a simple search in the current active worksheet to quickly find the data you need.
Use the keyboard shortcut Ctrl + F on Windows or Command + F on Mac. You’ll then see the Find tool appear on the top right of the sheet.
Enter the data you want to find and use Enter or Return to search the sheet. When the data is found, the containing cell will be highlighted. If there’s more than one match, you’ll see the count in the Find tool and can use the arrows to move up or down to each result.
Perform a Broader Search
If you want to search all sheets in the workbook or a certain cell range, the Find and Replace feature has you covered.
Open the Find tool on the top right as described earlier and select More options (three dots). Alternatively, you can select Edit > Find and replace in the menu.
When the Find and Replace dialog box opens, enter what you want to find (and optionally replace) at the top. Next to Search, choose All sheets, This sheet, or Specific range.
Select Find to search for the data. You’ll see Google Sheets maneuver to the sheet or cell and highlight the data it finds. Continue using the Find button to see additional results.
Use Find and Replace in Google Sheets
Maybe you want to find data and replace it with something else, match the letter case when you search, or look inside formulas for the search term. You can perform a more advanced search and use the replace option in Google Sheets.
Open the Find and Replace box as described earlier. Use the Find field for the data you want to search for and the Replace with field for what you want to replace the found data with. Then, select the Search area.
Next, you can check one or more of the boxes to effectively find and optionally replace your data. Let’s look at some examples.
Match Case
If you have data with both upper and lowercase letters, you can check the box for Match case to search more accurately.
As an example, we want to find “CITY” in our sheet. If we simply enter that word into the Find field, we’ll see results for CITY, New York City, and Kansas City which isn’t what we want. By default, the search isn’t case sensitive.
By checking the Match case box, we’ll see only the result highlighted for CITY in all caps.
If you want to replace that data with something else, this is simple. Here, we want to replace “CITY” with “Location.” Enter the Find and Replace with terms, check Match case, and select Find.
When we see the data highlighted, we select the Replace button to enter the new data.
Match Entire Cell Contents
If you’re only looking for partial data in a text string, the find feature will provide you with what you need. However, you may need to match exactly what’s in the cell to find the correct data. For this, check the box for Match entire cell contents.
For example, we want to find the Gmail address that’s missing the username. If you enter “@gmail.com” into the Find field, you’ll see results for every email containing “@gmail.com.” Again, this isn’t what we want.
If you check the Match entire cell contents box and select Find, you’ll go directly to that address you need that is missing the username.
To go a step further, you can add the correct email address in the Replace with field so that when you find the data, you can replace it with a click.
Here, we’ll search for “@gmail.com” and replace it with “homer@gmail.com.”
When we see the data highlighted, we select Replace to enter the new data.
Search Using Regular Expressions
While using Find and Replace in Google Sheets normally gives you the results you seek using basic language, you might prefer to use regular expressions. Google uses RE2 for regular expressions and you can view the syntaxes on GitHub if you like.
For an example, we have some phone numbers in our sheet in the wrong field. We’ll search for these phone numbers in the selected cells using this regular expression:
[0-9]{3}-[0-9]{4}
The expression breaks down as numbers zero through nine with three digits, a dash, and numbers zero through nine with four digits. This should find all results with that syntax which are our phone numbers.
We enter the expression in the Find field, choose Specific range in the Search field, and mark the checkbox for Search using regular expressions. (You’ll notice that when you check that box, the Match case box becomes automatically checked as well.)
Google Sheets then highlights each result as we select the Find button until no more results are found.
Search Within Formulas
You may need to find data that doesn’t exist within a cell but inside of a Google Sheets formula. For instance, maybe you want to find the cell where you applied the SUM function. In this case, you’d check the box for Also search within formulas.
Here, we want to find the previously mentioned function. If you simply enter “SUM” in the Find field, you’ll receive zero results.
When you check the Also search within formulas box, you’ll find exactly what you need.
What if you want to find that function because you need to replace it? We should have used the AVERAGE function for that cell range instead of SUM.
Here, we’ll search for “SUM” and replace it with “AVERAGE.”
When we see the data highlighted, we select Replace to enter the new data.
Search Within Links
One more option for finding the data you need is to look within links. This is helpful if you include a hyperlink that links to text where the URL isn’t displayed. For this, check the box for Also search within links.
For an example, here we have a cell containing the text “Click here” which is linked to Online Tech Tips. This gives us a fast way to visit our site.
Unfortunately, if you search for the word “Online,” you’ll receive no results.
Now, check the box for Also search within links and you’ll see the correct cell highlighted.
As with the above options, you can use the Replace tool when searching within links as well.
Tip: Keep in mind that if you have several results for your search and you want to replace each one the same, you can select Replace All.
No matter how simple or complex your search needs to be, you can find and replace data in just a few steps in Google Sheets. For more, check out our tutorial for how to find duplicates in rows and cells in Google Sheets.
