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
  • How to define search conditions
  • How to work with the results
• Related pages

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:

How to define search conditions

You will see the add-on pane offering you to fine-tune searching options:

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:

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.

   

   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

• Count and sum cells by their color
• Other features of the smart toolbar
• Find fuzzy matches in Google Sheets