How to Use Find and Replace in Google Sheets

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.

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.

You can perform a simple search in the current active worksheet to quickly find the data you need.

Use the keyboard shortcutCtrl + Fon Windows orCommand + Fon Mac. You’ll then see theFindtool appear on the top right of the sheet.

Enter the data you want to find and useEnterorReturnto 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.

If you want to search all sheets in the workbook or a certain cell range,the Find and Replace featurehas you covered.

Open the Find tool on the top right as described earlier and selectMore options(three dots). Alternatively, you can selectEdit>Find and replacein the menu.

When the Find and Replace dialog box opens, enter what you want to find (and optionally replace) at the top. Next toSearch, chooseAll sheets,This sheet, orSpecific range.

SelectFindto search for the data. You’ll see Google Sheets maneuver to the sheet or cell and highlight the data it finds. Continue using theFindbutton 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 theFindfield for the data you want to search for and theReplace withfield for what you want to replace the found data with. Then, select theSearcharea.

Next, you can check one or more of the boxes to effectively find and optionally replace your data. Let’s look at some examples.

If you have data with both upper and lowercase letters, you can check the box forMatch caseto search more accurately.

As an example, we want to find “CITY” in our sheet. If we simply enter that word into theFindfield, 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 theMatch casebox, 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 theFindandReplace withterms, checkMatch case, and selectFind.

When we see the data highlighted, we select theReplacebutton to enter the new data.

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 forMatch entire cell contents.

For example, we want to find the Gmail address that’s missing the username. If you enter “@gmail.com” into theFindfield, you’ll see results for every email containing “@gmail.com.” Again, this isn’t what we want.

If you check theMatch entire cell contentsbox and selectFind, 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 theReplace withfield 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 selectReplaceto enter the new data.

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 usesRE2 for regular expressionsand you canview the syntaxes on GitHubif 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 theFindfield, chooseSpecific rangein theSearchfield, and mark the checkbox forSearch using regular expressions. (You’ll notice that when you check that box, theMatch casebox becomes automatically checked as well.)

Google Sheets then highlights each result as we select theFindbutton until no more results are found.

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 forAlso search within formulas.

Here, we want to find the previously mentioned function. If you simply enter “SUM” in theFindfield, you’ll receive zero results.

When you check theAlso search within formulasbox, 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 selectReplaceto enter the new data.

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 forAlso 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 forAlso search within linksand 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 selectReplace 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 tofind duplicates in rows and cells in Google Sheets.

Sandy Writtenhouse is a freelance technology writer and former Project, Department, and Program Manager. She turned her education, job experience, and love of technology into a full-time writing career. With all sorts of gadgets in her home and her hands, she seeks to help others make life easier through technology.Read Sandy’s Full Bio

Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.

HomeAbout UsEditorial StandardsContact UsTerms of Use

Copyright © 2008-2024 Help Desk Geek.com, LLC All Rights Reserved