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.

Note. At the moment, this updated version of Advanced Find and Replace is available in the standalone add-on only. Coming to Power Tools soon! 😉

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: Run Advanced Find and Replace using the Google Sheets menu.

Find the add-on in Power Tools

If you're using Power Tools — our collection of 40+ add-ons for Google Sheets, follow these steps towards Advanced Find and Replace:

  1. Go to Extensions > Power Tools > Start to open the collection: Open Power Tools from the same 'Extensions' menu.
  2. Click on the Advanced Find and Replace icon on the Power Tools toolbar: Find Advanced Find and Replace on the Power Tools toolbar.

    or locate the tool in the Process group: Advanced Find and Replace in the Process group.

The add-on pane opens with the first tab — Search options — inviting you to fine-tune multiple different settings: Set the options according to the item you need to find.

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: Google Sheets: how to search for a word or number.

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 This add-on for Google Sheets searches for multiple values at once.

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: How to look for 2 or more phrases at once.

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"" How to search for phrases that include double-quotes.

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" Find lots of different words and phrases simultaneously.

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 and replace whole strings in Google Sheets.

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): Extra Google sheet with a list of items to find.

You take the range with these search data and enter it into the add-on including the name of the sheet:

states!A3:B15 Enter the range to find all records from it in other sheets.

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: Find states from the list in other sheets.

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 How to find and replace dates in Google Sheets.

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) Search by formula result to find all date mentions no matter their format.

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: Search by formula result to find all date mentions no matter their 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: Find all numbers that match your search comparison criterion.

2. Search specific types of data in Google Sheets

Narrow your search down to specific data types: Choose types of values to search within.

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. Search for 'Values' in Google Sheets including those that display 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). Find links and their labels when looking in 'Hyperlinks'.

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). Find and replace specific cell references in all formulas in Google Sheets.

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). Find all formula errors in Google Sheets.

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. Search the contents in notes in Google Sheets.

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: Select where in the file you'd like to find & replace.

  • 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: Add the range to find and replace in Google Sheets.

      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. Find and replace by formatting in Google Sheets.

  • 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. Make use of the additional search options.

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. 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 all fuzzy matches of the word 'apple' in your Google Sheets without formulas.

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.
Use wildcard search in Google Sheets.

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: See and handle the results in the corresponding tab.

  1. Next to the tab name Results, you'll find the total number of found items.
  2. 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.

  3. 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.
  4. 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.

  5. 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.
  6. Some extra options let you change the way results appear in the tree view: Tweak the appearance of the tree view with the results.

    Here you can switch between grouping the results by sheet or by search value: Group found records by sheet or by 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 Ranges with search and replacement words.

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: Settings to format the results.

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:   Options to delete the results.

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: Make use of plenty of ways to extract found records.

You can also choose to preserve text formatting during the export: Export rows with all found values to a new sheet preserving their text formatting.

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.

Related pages

Responses

Dominic says:
July 14, 2024 at 11:42 pm

Could you please include deleting cells with found entries or selected entries alongside being able to delete the whole row? Sometimes other cells in the rows of the found entries have important information and i just want to delete the cells with the found entries not the whole row.

Natalia Sharashova (Ablebits Team) says:
July 15, 2024 at 6:27 am

Hello Dominic,

To clear cells with the found values, you can just replace them with 'nothing'. Just keep the 'Replace with' field empty & make sure to tick off the checkbox 'Replace the entire cell' (above the 'Replace' buttons). Then hit Replace. Done 😊

John Johnson says:
February 24, 2023 at 7:48 pm

If you could add formatting to the replace I'd be interest in your app. From what I read you also replace current formatting.

Everton says:
May 24, 2022 at 2:36 pm

There is a way to replace any cell greater than a value?? like: if the cell is bigger then 10, replace this with another value

James says:
February 25, 2021 at 5:34 pm

Hi there,

I am trying to find an advanced feature for a find + replace function, and wondering if this can be achieved at all via this extension?

I have a spreadsheet of names (of people who organize events), each with their own unique ID number, and a second sheet of event names, each with a column stating the ID number of each event organizer who was involved in that event.

I am looking to run a find and replace so that the organizer's ID number in the sheet of events is replaced with the event organizer's name.

Is anything like this possible, or would this have to be achieved by a script of some sort?

Thanks!

Katerina Bespalaya (Ablebits Team) says:
February 25, 2021 at 5:57 pm

Hi James,

Thank you for your question.

With Advanced Find & Replace tool, you will need to search for each unique ID individually and replace it with the corresponding organizer's name. I'd recommend to try out the Merge Sheets tool instead. Since there is a common column with IDs in both sheets, you can combine them based on this column and choose to add the column with organizer's names to your second sheet. Please check out this online help page for the tool:
https://www.ablebits.com/docs/google-sheets-merge-two-sheets/

If you have any other questions or need further assistance, please email us at support@ablebits.com.

Rob Callahan says:
August 6, 2020 at 4:18 pm

I enabled Find and Replace to search within formulas, but it did not work for everything. For example, My formula is:
=PRODUCT(-1,SUMPRODUCT(Filtered!$I$2:$I$442,--(TEXT(Filtered!$A$2:$A$442,"MMM") = B$1),--(Filtered!$I$2:$I$442 < 0),--(Filtered!$B$2:$B$442"Reallocation of Funds")))

I can search and find "PRODUCT", but I cannot find "422". Why is that?
Thanks,
Rob

Paul says:
April 9, 2020 at 11:14 pm

Can I search for a string and replace it with the same string plus a line feed?

Katerina Bespalaya (Ablebits Team) says:
April 10, 2020 at 9:57 am

Hello Paul,

Thank you for for your question. Our Advanced Find & Replace tool can search for a string and replace it with a string + a line break. To enter a line break into the "Replace with" field, please use Alt+Enter or type in [Line break]. If you have any difficulties and need further assistance with your task, please send us a screenshot with your string sample to support@ablebits.com so that we'll help you better.
Thank you.

DEAN R SMITH says:
January 16, 2020 at 7:05 pm

I'm using Find and Replace across a workbook with about 20 sheets. There are no formulas or calculations to speak of. I have removed all unnecessary cells from each sheet but when I do a search it takes an incredibly long time. Many of the cells on each sheet have formatting of some kind. How does that slow down the search process or must it be something else?
Thanks.

Ekaterina Pechyonkina (Ablebits Team) says:
January 17, 2020 at 2:15 pm

Hello Dean,

Thank you for contacting us. Sorry to hear you are having issues with our add-on.
For us to be able to help you better, please share a small sample spreadsheet with us support@4-bits.com (1 sheet would be enough, if all of them have the same structure). One of our developers will try to reproduce the problem on our side and find its cause.

Abigail says:
January 5, 2020 at 5:48 am

Is there a way to find and replace across multiple workbooks? I have a folder of 90 documents which all have the same error that needs to be replaced and I’m trying to find a way to avoid opening each one individually and doing a find and replace.

Ekaterina Pechyonkina (Ablebits Team) says:
January 5, 2020 at 10:39 am

Hello Abigail,

Thank you for contacting us. Please note the add-on works for open sheets only. You can see a list of all open sheets in the sidebar and select checkboxes next to them. The Replace all button will switch all found results to the new entry at once.

Jamie says:
January 18, 2021 at 3:24 pm

Hello Ekaterina, I have the same question as Abigail, can you do an search and replace across many workbooks. You said "Please note the add-on works for open sheets only". Do you mean worksheets or workbooks? Are you saying that if I open all 30 of my work books, the tool could then do a search and replace across them all?

Justin says:
July 11, 2019 at 4:32 pm

Is there a way to find all cells of a specific color? Or find cells based on a range of values (ex: I need to find and replace all cells that have values within 1-50)?

Thanks

Katerina Bespalaya (Ablebits Team) says:
July 11, 2019 at 5:58 pm

Hello Justin,

Unfortunately, we do not have a tool that can find all cells of a specific color. As for the search based on a range of values, our Find and Replace add-on won't help with this task either. I can only recommend you to try the search by mask. For example, if you enter 1? in the Find what: field, the add-on will display all cells that contain numbers from 10 to 19.

Shah says:
July 9, 2019 at 10:07 pm

Is there a way to replace all of my options with just a blank cell?

Thanks

Natalia Sharashova (Ablebits Team) says:
July 10, 2019 at 7:01 am

Hello Shah,

Sure. Simply do not enter anything into the Replace with field. Just click Replace all right away - all found values will be replaced with blanks.

Daniel says:
April 29, 2020 at 3:37 am

What if you want to delete a specific row or column that has a specific set of characters and not just leave an empty space where that cell is.

Natalia Sharashova (Ablebits Team) says:
April 29, 2020 at 5:58 am

Hello Daniel,

I'm sorry, I'm afraid this feature is currently unavailable in the add-on. But since it's a common request, we are going to add it to the next version of the tool.

In the meantime, you could check out another tool – Multiple VLOOKUP Matches. It can pull entire rows if cells there don't contain specific info. Perhaps you'll find it helpful. Please read more about the add-on here: https://www.ablebits.com/docs/google-sheets-multiple-vlookup-matches/

Andrea says:
April 1, 2019 at 4:44 am

How do I find any text and replace all text with a single word eg.

I like dogs
Dogs are great
I want a dog

If I searched for the word "dog" and wanted to replace the whole cell with just the word "dog".

Natalia Sharashova (Ablebits Team) says:
April 1, 2019 at 9:37 am

Thank you for your interest in our product, Andrea.

When you set searching criteria, please pick to search By mask and enter the following:
*dog*

The add-on will find all cells сontaining "dog", no matter what's written before or after that.
The replacement will then change the entire cell contents with whatever you need. :)

Mishal says:
March 16, 2019 at 6:50 pm

Hello,

I have a spreadsheet with phone numbers, and I want to change the start of the number to a country code, but I can't find a way to do that. ex. numbers starting with 21 and wanting to add 216 behind that number.

Thank you

Natalia Sharashova (Ablebits Team) says:
March 18, 2019 at 12:06 pm

Hello Mishal,

Our Advanced Find and Replace and Add text tools can help you solve the task:

  1. Add an ampersand (&) at the beginning of your cells using Add text by position.
  2. With Advanced Find and Replace, find all mentions of &21 and replace them with 21_country_code.
  3. Delete all remaining ampersands with the Remove tool.

Hope this helps.

jonah keener says:
January 14, 2019 at 12:53 am

I'm trying to find the cases in my sheet where "u" is followed by "ui" the problem is that "u" and "ui" are in different cells and I'm looking for cases when they are next to each other. How would I go about doing this?

Thank you, I hope I'm being clear enough.

Natalia Sharashova (Ablebits Team) says:
January 14, 2019 at 8:50 am

Thank you for your interest in our product, Jonah.

I'm afraid our Advanced Find and Replace cannot search for values based on records in neighboring cells.
I can think of a couple workarounds though, with merging and splitting or exporting the data. But for me to be able to advise you better, please share a small sample spreadsheet with us (gapps.ablebits@gmail.com) with your source data and the result you expect to get. I kindly ask you to shorten the table to 10-20 rows.

I'll look into your task and see if our software can help.

Mark Broughton says:
November 6, 2018 at 4:15 pm

How do I Find and Replace duplicate Line Feeds in the text in a Cell?
Cntl+J works in Excel but not in Google Sheets. I have tried Alt+010 and Alt+013 too but I must be doing something incorrectly.

Natalia Sharashova (Ablebits Team) says:
November 8, 2018 at 9:31 am

Hello, Mark,

Thank you for your question.
If I understand your request correctly, you're trying to find cells where line breaks are used. To do that, place the cursor into the Find what field in the add-on and press Alt+Enter. Or go ahead and paste the following directly into the search field:
[Line break]

In case your task is more complicated than that, please share your sample spreadsheet with us - gapps.ablebits@gmail.com - with your example data and the result you expect to get. I kindly ask you to shorten the table to 10-20 rows.

We'll look into the task and do our best to help.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.