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 Add-ons > Advanced Find and Replace > Start in the Google Sheets menu:
Tip. You can find the add-on in
Power Tools as well:
- Go to Add-ons > Power Tools > Start to open the utility:
- Click on the Advanced Find and Replace icon on the Power Tools toolbar:
or locate the tool in the Process group:
How to define search conditions
You will see the add-on pane offering you to fine-tune searching options:
- Type the entry you are looking for in the Find what field.
- 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.
- 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.
- 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:
- The Type column here shows whether the found entry is a Value, Formula, Note, or a Hyperlink.
- 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.
- 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.
- The button with three dots in the upper right corner of the result list contains some extra options:
- Choose whether you need to see a cell address or type of value.
- Tick off the Preserve text formatting option to prevent values in cells from losing their formatting (bold, italic, linked text, etc.) upon the replacement.
- 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.
Related pages
Responses
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!
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.
Is there a way to execute the hotkey without pressing the start button?
Hello,
Thank you for your question. I'm really sorry, the add-on doesn't have the functionality you described. I wish I could assist you more.
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
Hello Rob,
Thank you for your comment. We re-checked the add-on's work in this scenario and all the parts of the formula were found correctly. For us to be able to understand the issue you faced better, please take a screenshot of the tool's pane with all the options selected and send it to support@alebits.com. Thank you!
Can I search for a string and replace it with the same string plus a line feed?
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.
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.
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.
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.
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.
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?
Hello Jamie,
Thank you for contacting me. The add-on will work for the worksheets (called 'sheets' in Google Sheets) of one open workbook (called 'spreadsheet' in Google Sheets). Sorry for any confusion from my side.
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
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.
Is there a way to replace all of my options with just a blank cell?
Thanks
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.
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.
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/
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".
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. 🙂
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
Hello Mishal,
Our Advanced Find and Replace and Add text tools can help you solve the task:
Hope this helps.
Hey Natalie,
Thanks for the reply, this worked perfectly. Really appreciate the fast responds of the Ablebits.com Team.
Keep up the good work.
You're most welcome, Mishal.
Glad we could help! 🙂
Hello,
Can I replace with formatted text in Google Sheets?
Thanks
Hello Marc,
Unfortunately it is impossible to preserve formatting when replacing text in Google Sheets.
Please contact us again if you have any other questions.
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.
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.
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.
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.