How to find and replace data in Google Sheets
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.
Before you start
Backup copies
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.
Start Advanced Find and Replace
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
How to set up search criteria
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.
1. How to enter search values in the 'Find what' field
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.
Find multiple values in Google Sheets
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:
Find multiple values in Google Sheets from the range
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:
How to find dates in Google Sheets
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)
Find formula results or numbers by condition
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:
2. Search specific types of data in Google Sheets
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.
Look for values
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.
Find links
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.
Find and replace in formulas
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).
Errors (error descriptions)
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).
Notes
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.
3. How to search in a column, sheet, or all tabs in Google Sheets
In the Search within section, you can limit and expand the extent of your search:
- Current sheet: focus on a single sheet only — the one you're viewing when you hit the Find button.
- All sheets: search all tabs within this Google Sheets file, including hidden sheets.
- Selected range: select a specific range of cells before initiating the search, and the add-on will target this selected area only. For instance, search specific cells or a column in Google Sheets.
- Specific ranges: scan certain ranges from different sheets. This way lets you search across multiple sheets in Google Sheets. There are three ways to specify the ranges:
- Enter multiple ranges in the field manually (separated by commas).
- Pick one of the previously used set of ranges from the drop-down list (the down arrow on the right side of the field will open that list for you).
- Click the Add range icon at the end of the field. You'll see a pop-up window allowing you to pick the necessary cells:
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.
4. Search cells by formatting
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.
- Bold
- Italic
- Underline
- Strikethrough
- Fill color of your choice
- Font color of your choice
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.
5. How to look for partial matches
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:
- Match case to make your search case-sensitive.
- Entire cell to find cells that contain nothing but the entered word or phrase.
Find fuzzy matches in Google Sheets
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.
Find and replace using wildcards in Google Sheets
Toggle By mask on to search for records in certain positions by patterns. The following wildcard characters will help:
- * — an asterisk stands for a string. For example, if you want to find cells that begin with the number 24, enter 24* into the Find what field.
- ? — a question mark denotes one character. Enter 24??? to find cells that contain the number 24 followed by any three characters.
- feel free to combine both. ??? * (with a space in-between) will find such cities as New York, Los Angeles.
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.
Find and replace using regex (regular expressions)
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)
How to work with the results
You will see all found values grouped by sheet on the Results tab:
- Next to the tab name Results, you'll find the total number of found items.
- Click the Selection mode icon to see checkboxes for all found entries. The number of selected entries will appear at the bottom of the tree view.
- Tick the boxes next to certain values to manage only some results.
- The box next to a sheet name will select all records found on that sheet only.
- Clicking the box at the very top will select all results from all sheets in the tree view.
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.
- To navigate to the record in the sheet, click on it in the list, or refer to the Cell column to see where it's located.
- Enter the new values you want instead of the old ones in the Replace with field.
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.
- While the tool replaces only found values even if they're just part of cells, you can still replace the entire cell content (not just the found entry) if you select this box.
- Some extra options let you change the way results appear in the tree view:
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).
Find and replace multiple values in Google Sheets
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.
Find and replace formatting (change color)
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:
Delete rows with found values
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:
Export found values
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:
Refresh the results
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