The Advanced Find and Replace add-on for Google Sheets looks for any value you need all over your sheets or in the selected ranges. Scan your data for values, specific formatting, formulas, notes, hyperlinks, or even errors. A handy tree view lets you navigate between the found records easily. Replace some or all of them at once, color or format found cells, delete or export the results to a new sheet, file or a range of cells — everything is within one tool.
Google Sheets won't let you undo the changes made by this add-on. So if you'd like a quick way back to the original, either duplicate sheets before replacing anything or be ready to restore the required version from the version history.
Open the add-on from the Google Sheets menu: Extensions > Advanced Find and Replace > Start:
If you're using Power Tools — our collection of 40+ add-ons for Google Sheets, follow these steps towards Advanced Find and Replace: or locate the tool in the Process group:
Find the add-on in Power Tools
The add-on pane opens with the first tab — Search options — inviting you to fine-tune multiple different settings:
Below you will find detailed instructions with examples of using every setting for a precise search.
First, you must type whatever you want to search in Google Sheets in the Find what field.
Tip. Click on the i icon above the field to see a tip on how to enter different types of data.
To search for a number or word in Google Sheets, enter them directly into the field, e.g. 42 or Hawaii:
But there's a lot more you can find and replace in Google Sheets with this add-on. Let's dig deeper.
Example 1. To find all instances of one-word records, enter them all separated by space, or comma, or semicolon:
Alaska Hawaii Texas
or
Alaska,Hawaii,Texas
or
Alaska;Hawaii;Texas
Example 2. To find multiple values in Google Sheets as phrases, put them in "double quotes":
"New York" "New Jersey"
This way the add-on will understand which delimiter (space in this example) is part of your phrase and which one is used to distinguish those records in a search string:
Example 3. Want to look for double quotes as well? Simply wrap your whole search input (including double quotes) in — well — double quotes :)
"Project "Alpha"" "Project "Beta""
Example 4. Feel free to nest as many words, phrases (in double quotes) and numbers as you need to find multiple different values in Google Sheets:
"New York" NJ Alaska "South Carolina"
Example 5. Enable Search as a whole string setting to turn off delimiters used by the tool to search for multiple strings simultaneously. The add-on will locate phrases like California, San Francisco as a whole, disregarding separators (like commas and spaces) that are used for individual word searches by default:
Instead of listing multiple values in the Find what field, you can neatly arrange them somewhere in your spreadsheet and supply their range to the add-on instead.
Here's an example: you have 4 sheets. The first 3 contain datasets you work with. The 4th one is your helper sheet with the records you'd like to find in all other sheets (a list of specific states, or items, or IDs, for instance):
You take the range with these search data and enter it into the add-on including the name of the sheet:
states!A3:B15
Note. Make sure to specify a sheet name to avoid unexpected results when looking within the Current sheet.
Also, don't enter the equal sign at the beginning — it will turn the range into a formula and won't work as you expect.
The add-on will look for the contents of every single cell from that range in the required tables:
The updated Advanced Find and Replace lets you enter dates exactly as they appear in cells:
02/14/2024 or 14 Feb 2024 or 02.14.2024
Note. The search is based on the displayed format. Hence, the date formatted as 02/14/2024 won't appear in the results if you search for 02.14.2024.
Tip. To find all mentions of the date no matter the format, use the DATE function as your search criterion:
=DATE(2024,02,14)
While it may not be a common practice to look for formula results, there are certainly cases where this type of search can be beneficial for data accuracy, analysis, and even debugging formulas.
To look for formula results in your data, you enter the formula directly in the Find what field. The add-on calculates its result once and finds its matches in the desired range.
One of the most vivid examples of searching using formulas is when you look for particular dates or times. For instance, using the formula =DATE(2024, 2, 14) will find all instances of February 14, 2024, regardless of their number format:
The same goes for using comparison operators in your search criteria:
>, <, =, >=, <=, <>
They can be useful to filter out numbers that fall within certain ranges or meet specific criteria such as >=50:
Narrow your search down to specific data types:
Tip. Select all checkboxes to search for data everywhere simultaneously.
Tip. Leave the Find what field blank to find all instances of the selected type of data, e.g. tick off Errors to find all errors.
Select Values to look in text, numbers, or dates you see in cells.
Note. If your search range contains links (like clickable email addresses), they will also appear in the result as values. Why? Because each link consists of a displayed text and a URL behind it. The displayed text is the one that fits the 'value' criterion.
Select Hyperlinks to look in all URLs and URL labels (clickable text, numbers, email addresses, and other content you can see in cells that direct you to another cell, sheet, spreadsheet or webpage).
Tip. Leave a blank Find what field and mark the Hyperlinks checkbox to find all hyperlinks in the desired range.
Select Formulas to scan actual equations or functions used to perform calculations. Formulas start with an equal sign (=), e.g. =SUM(A2:A42)
This search will let you quickly replace formulas in Google Sheets by updating cell references and other data within (hence, get the updated calculations).
Why search within errors? Well, if you leave a blank Find what field, the add-on will find all errors and give you a list so you know something went wrong in those cells and could address the issues.
Select Errors to include them in your search (e.g. #N/A) along with their descriptions (error details that appear when you hover your mouse over them).
Tip. Searching within error details may be useful if you want to check if a particular error exists in your table but you don't remember its actual name for some reason. You may remember some words from its description though (reason for error).
Tick off Notes to search those extra annotations that appear as small black triangles in the upper-right corner and that pop up when you hover your mouse cursor over cells.
Tip. Leave a blank Find what field and tick the box Notes. The add-on will find the text from all notes existing in the required range.
In the Search within section, you can limit and expand the extent of your search:
The Auto select button will identify your whole table (a used range till the first blank row and column) automatically.
Repeat for every desired range.
In addition to searching for specific items, Advanced Find and Replace also allows you to look for cells based on their formatting. It's incredibly useful to quickly locate and manage cells that share the same visual attributes, such as red fill color, bolded headers, underlined text, etc.
You can either search for cells that match specific formatting criteria ignoring their content or find specific values that are formatted in a certain way.
If you select multiple options here, they will work as a blend of conditions. In other words, the tool will look only for cells with all chosen formatting applied.
Tip. Leave the Find what field blank to find all instances of the selected format. For example, tick Bold in Format options to find all bold text.
Note. At the moment, the add-on doesn't support partial cell formatting (rich text) due to memory constraints. It will only look for formatting applied to entire cells.
Advanced Find and Replace provides several tools to help with partial matches including case matching, fuzzy search, masks, and regular expressions. All these let you refine your search criteria, making it easier to locate specific data even if it doesn't exactly match your initial query.
Two checkboxes at the top are:
Fuzzy search will find approximate matches for your entry. It's convenient when the exact content is unknown or when dealing with spelling variations or typos.
Toggle Fuzzy search on and specify the number of fuzzy characters allowed (up to 5). The add-on will find those fuzzy matches for your search query.
Example. Suppose you're looking for the word apple, but some instances are misspelled like aple or appl. By enabling fuzzy search with a setting of 1 fuzzy character allowed, the add-on will also match these similar variations, ensuring you capture all relevant instances of the word apple in your Google Sheets.
Toggle By mask on to search for records in certain positions by patterns. The following wildcard characters will help:
Note. Masks in the add-on are greedy. This means they will match the longest possible string that fits the pattern.
Example. If your cell contains several instances of text in brackets, like this:
Palau (click for pronunciation), officially the Republic of Palau (Palauan: Beluu er a Belau), is an island country in Oceania.
and you use the mask (*) to find and replace each pair of brackets with text in-between, this mask will match (and replace) everything from the first opening to the last closing bracket:
(click for pronunciation), officially the Republic of Palau (Palauan: Beluu er a Belau)
For a more precise search (non-greedy) that will stop at the first closing bracket and will find other shortest possible matches within each cell, use regular expressions described below.
Regular expression enables the use of regular expressions in your search queries. Regular expressions provide advanced pattern-matching capabilities for more flexible and precise searches. With this setting, you can use powerful search patterns to find and replace text in Google Sheets according to specific criteria.
Example 1. A regular expression like \d+ will match one or more digits and find all numerical instances like 123, 4567, 7890 within the text.
Example 2. A non-greedy search pattern like \(.+?\) will find all instances of brackets with text in-between in each cell (something masks above cannot do).
So in this cell:
Palau (click for pronunciation), officially the Republic of Palau (Palauan: Beluu er a Belau), is an island country in Oceania.
It will treat both instances individually:
(click for pronunciation)
(Palauan: Beluu er a Belau)
You will see all found values grouped by sheet on the Results tab:
Tip. You can still select multiple entries even without checkboxes. Just hold the Ctrl key on your keyboard when selecting them in the tree view one by one.
To select multiple adjacent entries, click the first one in the list, press and hold Shift, and click the last entry in the list. All records in between will be selected automatically.
Tip. See the sections below to learn how to replace multiple different values at once and how to format, delete or export results.
Click Replace all button at the bottom to switch all found results to the new entry. Or select only some items you'd like to replace and click Replace.
Tip. You'll find the Skip button useful if you're managing results one by one using your keyboard.
Here you can switch between grouping the results by sheet or by search value:
You can also show or hide a cell address and type of value (whether the found entry is a Value, Link, Formula, Note, or Error).
To replace multiple values, feel free to enter multiple replacements for each search term separated by space/comma/semicolon.
For example, you want to find and replace apple with plum, carrot with celery, pear with apricot. Your search criteria will look like this:
apple carrot plum
It's 3 words separated by space. Your replacement criteria will look just the same: 3 new words separated by space in the order related to the words from the search:
plum celery apricot
Tip. If your search & replacement words are organized in a table, simply enter the ranges with these words into the corresponding Find what and Replace with fields, e.g. A1:A3 and B1:B3
The add-on will replace entries with their replacements one after another, in the order of appearance.
Three buttons at the top offer extra ways to deal with the results. One of them lets you color found cells or/and change their text format: font color, make text bold, italic, underlined, strikethrough:
If the rows with the found values are no longer needed, you can easily delete all of them or only certain selected ones. The corresponding option at the top of the list with found records will see to it:
Advanced Find and Replace offers one more powerful feature — export, which is indispensable for managing large datasets.
It acts like a filter, isolating relevant items and making it easier to focus on and analyze specific data. By working on extracted data independently, you reduce the risk of errors because your original sheet remains intact.
Once Advanced Find and Replace locates the values you're looking for, you decide whether to export all found or selected entries, or entire rows containing those records, to a new sheet, a new spreadsheet, or a specific range of cells within your current file:
You can also choose to preserve text formatting during the export:
If you want to search for the same type of data in the same sheets again, change the search term in the Find what field at the top and click Find to refresh the results.
To start a new search with different settings, just switch back to the Search options tab.
Please contact us here