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 spreadsheets or in the selected range. Scan your data for formulas, notes, hyperlinks, or errors. A handy tree view lets you navigate between the found records easily. Replace some or all of them at once, delete or export the results to a new sheet — everything is within one tool.

Video: Find and replace data in Google Sheets

Before you start

Advanced Find and Replace doesn't process dates to avoid data corruption. The thing is, the add-on don't get dates the same way you see them. All dates appear as a combination of numeric characters that differ from the date format displayed to you in the sheet, e.g. the add-on sees "Mon May 05 2014 00:00:00 GMT" instead of "5/5/2014". This is the reason why neither search nor replacement will return correct results for dates.

How to find and replace in Google Sheets

Start Advanced Find and Replace

Open the add-on from Extensions > Advanced Find and Replace > Start in the Google Sheets menu:
Run Advanced Find and Replace using the Google Sheets menu.

Tip. You can find the add-on in Power Tools as well:

  1. Go to Extensions > Power Tools > Start to open the utility:
    Open Power Tools using the same Add-ons menu.
  2. Click on the Advanced Find and Replace icon on the Power Tools toolbar:
    Find the add-on on the smart toolbar in Power Tools.
    or locate the tool in the Process group:
    Advanced Find & Replace in the Process group.

You will see the add-on pane offering you to fine-tune searching options:
Set the options according to the value you need to find.

  1. Type the entry you are looking for in the Find what field.
  2. Pick some additional options:
    • Make the search case-sensitive by selecting the Match case option.
    • Use the Entire cell option to find cells that contain nothing but the entered word or phrase.
    • Tick off the By mask checkbox to search for values in certain positions. The following wildcard characters will help:
      • * — an asterisk stands for a string. For example, if you want to find cells that begin with number 24, enter 24* into the Find what field.
      • ? — a question mark denotes one character. Enter 24??? to find cells that contain number 24 followed by any three characters.
  3. Narrow your search down to specific data types: Values, Formulas, Notes, Hyperlinks, or Errors.
    Tip. Tick off all checkboxes in the Look in section to search for the data entry everywhere simultaneously.
  4. You can see a list of all open sheets in the sidebar. Select checkboxes next to any sheets you want to include in the search.
    Tip. To find all instances of the entered word in the entire spreadsheet, use the Search in checkbox at the top: this will quickly select all sheets.

    It's also possible to limit the search to the selected range with the corresponding Selected range option.

Click the Find all button to work with the results.

How to work with the results

You will see all the found entries grouped by sheet on the Search results tab:
Replace the results with the values of your choice.

  1. The Type column here shows whether the found entry is a Value, Formula, Note, or a Hyperlink.
  2. To navigate to the record in the sheet, click on it in the list or use the Cell column to see where it is located.
  3. Enter the new value you want instead of the old one in the Replace with field.

    Click Replace all to switch all found results to the new entry or select the values you'd like to replace in the search results and click Replace.

    Tip. You can highlight multiple entries by holding the Ctrl key on your keyboard and selecting them in the sidebar tree view.

    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 the records in-between will be highlighted automatically.

    Tip. You can quickly select all values found in a given sheet by clicking on its name in the results.
  4. The button with three dots in the upper right corner of the result list contains some extra options:
    Additional options to export the found values.

    1. Choose whether you need to see a cell address or type of value.
    2. Tick off the Preserve text formatting option to prevent values in cells from losing their formatting (bold, italic, linked text, etc.) upon the replacement.
    3. Export all found or selected entries, or entire rows with the values to a new sheet. Or completely delete whole rows with all or selected found entries.
    Tip. You can highlight multiple records in the results by holding the Ctrl key on your keyboard.

    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 the records in-between will be highlighted automatically.

If you want to search for the same type of data in the same sheets, change the search term in the Find what field at the top and click Find all to refresh the results.

To start a new search with different settings, press New search in the bottom right corner of the sidebar.

Responses

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.

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.