Advanced search and quick replacements in Google Sheets

Learn about our recent addition to Power Tools that lets you perform an advanced search in the entire spreadsheet, export rows with all found values, and replace any records you need.

We're not always in control of the way we get our data. Even if the layout is non-standard, the basic tools may not do their part. When it comes to finding and processing particular records, Advanced Find and Replace shows its best. We designed it with certain tasks in mind, and in this post, I will show how you can easily retrieve any values from your spreadsheet, no matter where they are hidden, when you have this tool at hand.

Here is an example of how intricate our data can get: I have the book type specified in notes, within order links, and as part of cell values:
Find text in notes, errors, links, formulas, or values.
How do I go about seeing all hardcover books from several sheets?

First, let's follow to the Add-ons menu item and start Power Tools; the first icon you click at the top of the sidebar will open Advanced Find and Replace.
Start Advanced Find and Replace from Power Tools.
Now you can specify your exact search criteria and work with the results.

All the search options you need

Let the number of settings in the sidebar not scare you off, they are there to help you. Three main groups of options let you make a very precise search request:

  • Find what field is pretty straightforward: here you enter the term you are looking for, like "hardcover".

    If you look for values that depend on position or adjacent information, you will be pleased to employ the two most commonly used wildcard characters. Once you check off By mask, the question sign turns into any single character, and an asterisk will stand for any string. Thus, you get plenty of variations to make your search term as accurate as possible.

    You can still find question and asterisk signs if you leave "By mask" unchecked.

    Match case allows you to make your search case-sensitive. Enter the term in any case, it's this checkbox that allows you to ignore uppercase or take it as a reason to filter out some of the results. So if I enter "Hardcover" with this option selected, I won't get any records that have the term in lowercase.

    Once you select Entire cell, you will get only those matches that don't contain anything except your searched record.
    Advanced options to search in Google spreadsheet.

  • Now, if you look at the next group of checkboxes, you will discover that they are very easy to understand. The add-on can check usual cell values, the contents of the formulas, notes, errors, and the text hidden behind hyperlinks. You can expand the search to all types of data, or limit it to just some.
  • Finally, we define the range of data to check. I pick the combination of sheets where I want to find my text, and you are free to search within a column or the entire spreadsheet if you like.

Pick your search results

Now that I'm set with my criteria for the search (and it really takes just a few seconds), I click Find all and get to my favorite part of this tool: the entire list of matches right before my eyes.
View all found items in one list.
Now I can see how the land lies: notice if there are many matches in each sheet, what type of data is prevalent, and so on. What makes it even more convenient is that you can go to any of the records by simply clicking on it. Besides the overview and navigation, there are several other things you can do.

  • Since it's not possible to change the sidebar size in Google Sheets, you can save some space for the found values: click on three dots in the header to decide if you want to display the cell address and data Type columns.
  • The second option will let you substitute found records with new ones while keeping their formatting intact.
  • Substitution is not the only thing you can do here, the add-on lets you manage the results pretty well. If you want to get the summary of all found entries in a separate sheet - feel free to do so. Click the same three dots to see four different export and two removal options. Click Export all found entries to get the list of found items the same way you see them in the sidebar:
    Export search results to a new Google Sheets.
  • The next option is extremely convenient for extracting the found data with all adjacent information: you can pull entire rows from your table to a separate sheet. This is perfect when your data are too complex to use a filter, so you can search by the keyword instead.
  • Of course, the possibility to clear up the found records is on the menu. The Delete rows with all found entries option will do that for you.
  • One hidden treasure is the possibility to select several found records in the list. It may seem obvious to some of you, but just in case you missed it, hold the Ctrl button on your keyboard (Cmd if you have a Mac), and pick the entries to replace, export or delete.
  • Well, and of course you can replace any found values. Use the field at the bottom to enter the substitution and either click Replace all to change all items, or Replace if you update just the selected entries.

If your search criteria don't change but you want to find several different values, you can update the search term at the top and click Find all again. This field will also store your recent terms to use again. In case the search criteria change, click New search at the bottom to modify the conditions.

If you want to see more time-savers, click on its icon again and discover what Power Tools has to offer.

Video: Advanced Find & Replace add-on for Google Sheets

Here I also have a video that demonstrates how Advanced Find & Replace looks like and works in spreadsheets.

Note. Though the video shows a bit old version of the add-on, it still represents its main possibilities. To see what new options have been added so far, please visit the add-on help page.

I hope this tool will help you with your next search. Your questions are welcome!

See also

2 comments to "Advanced search and quick replacements in Google Sheets"

  1. Sian Brook says:

    I am wondering if there is a way to automatically export certain data that meets criteria as sheet continues to update and add.

    For example if I have a spreadsheet about sales and I want to export all data relating to sales from New York. I find and export that data to new sheet. But on my master sheet I keep adding information including information about New York. How do I automatically have my separate New York sheet automatically import from master sheet?


    • Thank you for your interest in our product, Sian.

      I'm afraid it's currently impossible to run our add-on automatically. You need to run it each time you want to find and export data.
      However, since this is a common request, we do consider adding trigger support or scenarios to automate repetitive tasks. I can't give you any timing, but we can let you know if/when the feature is available.
      Thank you.

Post a comment

Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)