Find and replace in Google Sheets: options, regex and examples
Find and replace changes a text everywhere it occurs in a range, a sheet or the whole file. Open it with Ctrl+H, type what to find and what to put in its place, and click Replace all. This guide explains each option of the dialog, how to search inside formulas, and how to use regular expressions, which take the place of wildcards.
To find and replace in Google Sheets:
- Press Ctrl+H (Cmd+Shift+H on a Mac), or click Edit > Find and replace.
- Type the text to look for in Find and the new text in Replace with.
- Choose where to search: all sheets, this sheet or a specific range.
- Click Replace all, or Find and then Replace to go through the matches one by one.
To search without replacing, Ctrl+F (Cmd+F) opens a small search box in the corner of the sheet.
The dialog, option by option
| Option | What it does |
|---|---|
| Search | The scope: All sheets, This sheet or Specific range |
| Match case | Makes the search case-sensitive: “pro” no longer finds “Pro” |
| Match entire cell contents | Finds only cells whose whole content equals the search text |
| Search using regular expressions | Reads the Find field as a pattern |
| Also search within formulas | Looks at the formula text, not only at the displayed result |
| Also search within links | Includes the links attached to cells in the search |
By default the search is not case-sensitive and matches any part of a cell. That second point is the usual source of accidents: replacing “Pro” with “Professional” also turns “Product” into “Professionalduct”. Either type enough of the text to make it unique, or tick Match entire cell contents.
Replace a value step by step
The Product column holds “Widget Pro” in several rows, and the name has changed to “Widget Professional”.
Open the dialog from the Edit menu or with the shortcut.
Enter “Widget Pro” in Find and “Widget Professional” in Replace with, leave the scope on This sheet and click Replace all. The dialog reports how many replacements were made.
To do the same in every tab of the file, set the scope to All sheets. To limit it to one column, choose Specific range and select the column; this is the safest setting, because nothing outside the range can change.
OWOX Data Marts
See your first report built in real time. 15 minutes.
- Connect your data warehouse
- Pick your metrics
- Get a live Google Sheets report
In the time it takes to write a ticket. Then imagine never writing that ticket again.
Book a DemoWe'll use your actual use case
Examples
Update a code everywhere
Find:
WP-1001
Replace with:
WP-1101
One pass replaces every occurrence of the code. Each different code needs its own pass.
Remove a character
Leave Replace with empty to delete what is found. Here the closing quotation mark is removed from a Notes column:
The same works for stray symbols, double spaces (find two spaces, replace with one, repeat until nothing is found) and unwanted prefixes.
Replace inside formulas
By default, Find and replace reads what the cells display. In a column built with =E3 & " only", searching for “only” finds nothing to change, because the text is produced by the formula. Tick Also search within formulas:
The formula text itself was edited: =E3 & " only" became =E3 & " ". The same option renames a sheet reference or a range in many formulas at once, for example replacing “Sheet1!” with “Data!”. Check a few formulas afterwards, since a replacement inside a formula can break it.
Wildcards and regular expressions
Wildcards and regular expressions
Find and replace does not use the * and ? wildcards of functions like COUNTIF. An asterisk typed into Find looks for an asterisk. Patterns are written as regular expressions, with Search using regular expressions ticked.
| Pattern | Finds |
|---|---|
. |
Any one character |
.* |
Any text, including none |
^SAVE |
Cells that start with SAVE |
10$ |
Cells that end with 10 |
[0-9] |
One digit |
[0-9]+ |
One or more digits |
\s |
A space or another whitespace character |
colou?r |
“color” and “colour” |
\$ |
A dollar sign; a backslash makes a special character literal |
Replace whole cells that match a pattern
Find:
.*10$
Replace with:
IntroDeal
.*10$ means “any text, then 10 at the end of the cell”. The whole match is replaced, so SAVE10 and DEAL10 both become IntroDeal, and the other codes stay as they are.
Keep a part of the match
Brackets in the pattern capture a part of the match, and $1, $2 in Replace with put it back. The example from Google’s help page (checked in October 2026) sets every date to the first day of its month:
Find:
(\d*)/\d*/(\d{4})
Replace with:
$1/1/$2
The date 3/8/2015 becomes 3/1/2015: the first group holds the month and the second the year.
For the same patterns in formulas, which leave the source untouched, see REGEXREPLACE.
Find and replace, or a formula
Find and replace, or a formula
Find and replace edits the cells themselves, once. A formula leaves the source as it is and repeats the change whenever the data changes.
- For a one-off cleanup of a finished table, use Find and replace.
- For data that is refreshed or re-imported, use SUBSTITUTE, REPLACE or REGEXREPLACE in a helper column, so that the change is applied to every new load.
- To locate a text and return its position or a TRUE/FALSE, use FIND and SEARCH.
Problems and how to avoid them
Problems and how to avoid them
⚠️ Problem: More was replaced than intended.
✅ Solution: The search matched parts of other words. Press Ctrl+Z at once; a Replace all is undone in one step. Next time, limit the scope to a range and tick Match entire cell contents or Match case.
⚠️ Problem: Nothing is found, although the text is visible.
✅ Solution: The text is the result of a formula (tick Also search within formulas), the scope is set to another sheet or range, or the cell holds a look-alike character such as a non-breaking space or a different kind of quotation mark.
⚠️ Problem: The pattern with * or ? finds nothing.
✅ Solution: Those are not wildcards here. Tick Search using regular expressions and write .* for any text and . for one character.
⚠️ Problem: Some cells did not change.
✅ Solution: Cells in a protected range cannot be edited by someone without permission. Ask the owner, or check Data > Protect sheets and ranges.
⚠️ Problem: A regular expression matches too much.
✅ Solution: .* takes as much as it can. Anchor the pattern with ^ and $, or replace the dot with a narrower set such as [0-9].
Related guides
- SUBSTITUTE: Replace a text with a formula.
- REPLACE: Replace by position.
- REGEX functions: REGEXMATCH, REGEXEXTRACT and REGEXREPLACE.
- FIND and SEARCH: The position of a text in a cell.
- TRIM, CLEAN and T: Remove extra spaces.
- Remove duplicates: The other Data cleanup tool.
- How to split cells: Split text into columns.
- Data validation: Keep values consistent at entry.
A replacement that has to be repeated is a rule
A replacement that has to be repeated is a rule
Find and replace is a one-time edit. When the same replacement is needed after every export, an old product name, a prefix, a status spelled two ways, it has stopped being an edit and become a rule about the data. Applied by hand it will be forgotten once, and that week the report will show two products where there is one.
The OWOX Data Marts extension for Google Sheets covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.
What changes for the person who maintains the sheet:
- Refresh replaces re-export. Refresh current report in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
- The source is written on the sheet. The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
- No SQL and no warehouse access needed. The analyst decides which data marts and columns are available. You choose from that list.
The extension does not write or change the analyst’s SQL. A rule written once in that SQL is applied to every refresh, and Find and replace goes back to fixing the occasional typo. The extension comes with the OWOX Cloud plans of OWOX Data Marts; spreadsheet reporting describes both ways data reaches a sheet.
Turn your data into decisions.
Governed data marts give you the clean foundation ML needs to actually work.
- No AI hallucinations
- Analyst-governed definitions
- Every number traces to SQL
Frequently Asked Questions
Can I undo a Find and Replace action in Google Sheets?
Yes, immediately press Ctrl + Z (Undo) after replacing to revert changes. If too much time passes, you can also use the Version history to restore the sheet.
Is there a way to use Find and Replace with wildcards in Google Sheets?
Find and replace does not use the * and ? wildcards of functions such as COUNTIF: an asterisk in the Find field looks for an asterisk. Tick Search using regular expressions and write the pattern as a regular expression instead: a dot stands for any one character, .* for any text, ^SAVE for cells that start with SAVE and 10$ for cells that end with 10.
Can I use Find and Replace for numbers and special characters?
Absolutely! You can find and replace numbers, punctuation, spaces, symbols, or even special characters—just enter them directly in the Find and Replace fields.
How do I match case when using Find and Replace in Google Sheets?
Enable the "Match case" checkbox to ensure Google Sheets only finds text that matches the exact capitalization you enter in the Find field.
Is it possible to find and replace text in formulas?
Yes, check the "Search within formulas" option in the Find and Replace tool to update text or values that appear inside formulas without changing their structure.
Can I use Find and Replace to update multiple cells at once?
Yes, you can apply Find and Replace to an entire sheet, all sheets, or a selected range – updating multiple matching cells in one step.
How do I use Find and Replace in Google Sheets?
Press Ctrl + H (Cmd + Shift + H on a Mac) or choose Edit > Find and replace. Enter the text to find and the text to replace it with, choose the scope (all sheets, this sheet or a specific range) and the options you need, then click Replace all, or Find and Replace to go through the matches one by one.
