Ukraine flag War in Ukraine. Here's what Ablebits is doing to make sure our team and projects are safe.

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:
    Adavanced 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.