Guides

Sheets Mega Tools

Find and Replace Values

Learn how to find and replace specific values throughout your Google Sheets™ spreadsheet using the Text To Table Converter add-on.

The Find and Replace Values tool allows you to search for values using regex or substring matching, then highlight, replace, format, or delete the matches found across your Google Sheets™ spreadsheet.

How to Use Find and Replace Values

  1. Open the Find Values feature

Navigate through the Google Workspace™ menu: Extensions > Text To Table Converter > Mega Tools > Find Values in Cells.

  1. Select your scope

Choose where to search:

  • Selection - Search only within your currently selected range
  • Current Sheet - Search the entire active sheet (default)
  • All Sheets - Search across all sheets in your spreadsheet
  1. Enter your search query

Type the value or pattern you want to find. The tool treats your input as a regular expression by default.

  1. Configure search options

Use the options panel to customize your search:

  • Exact match - Toggle to match the entire cell value exactly
  • Case sensitive - Toggle to distinguish between uppercase and lowercase
  • Search in - Select what to search:
  • Values (default: on)
  • Formulas (default: on)
  • Links
  • Notes
  • Errors
  1. Run the search

Click the "Search" button to find all matches.

  1. Review results

The results window displays:

  • Count of matches found
  • Table showing: Checkbox | Location (sheet!A1) | Type badge
  • Tooltips showing display values on hover
  • Collapsible results section
  1. Select matches and apply actions

Use checkboxes to select specific matches, then choose an action:

  • Highlight - Apply a background color to selected matches (color picker available)
  • Replace - Replace found values with new text
  • Format - Apply background or text color formatting
  • Delete rows - Remove entire rows containing matches (option to keep first result row)
  • Copy result - Copy the range and display value to clipboard

Default Settings

  • Scope: Current Sheet
  • Search in: Values and Formulas (enabled by default)
  • Other search fields: Links, Notes, Errors (disabled by default)
  • Regex mode: Input is treated as a regular expression

Tip: Results and your selection persist until you run a new search. Action buttons remain disabled until you select at least one match.

Large Ranges: For very large ranges, a warning will appear before running the search. You can choose to proceed or cancel the operation.

Use Cases

  • Find and highlight all instances of a specific value across your spreadsheet
  • Replace outdated product codes or names with new ones
  • Clean up data by deleting rows containing specific error values
  • Format matches to make them stand out visually
  • Search formulas to find cells using specific functions or references

Note: This tool is part of the Mega Tools suite, available exclusively in Google Sheets™.