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, or export the results to a new sheet - everything is within one tool.

Video: Find and replace data in Google Sheets

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:
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 Add-ons > 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's icon on the smart toolbar in Power Tools.

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. If you click on the button with three dots in the upper right corner of the result list, you will be able to:
    • Choose whether you need to see a cell address or type of value.
    • Export all found or selected entries, or entire rows with the values to a new sheet.

    Additional options to export the found values.

    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

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.

Reply

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.

Reply

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.

Reply

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.

Reply
Marc Williams says:
February 21, 2019 at 7:59 am

Hello,

Can I replace with formatted text in Google Sheets?

Thanks

Reply

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.

Reply

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

Reply

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.

Reply

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! 🙂

Ask a question (posted publicly)

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.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.