To find and replace in Google Sheets, click Edit, then Find and replace, type the word next to Find and the new word next to Replace with, then click Replace all. To search without replacing anything, press Ctrl + f, or ⌘ + f on a Mac.
Here's the catch. Sheets gives you four tick boxes that change what gets matched, and one of them, Search using regular expressions, does more in Sheets than anywhere else. Regex replacements, including capture groups and special characters like \n for a new line, only work in Google Sheets. In Google Docs, the same pattern typed into Replace with is inserted as literal text.
Find and replace, step by step
- On your computer, open a spreadsheet in Google Sheets.
- Click Edit, then Find and replace.
- Next to Find, type the word you want to find. To replace it, enter the new word next to Replace with.
- Click Find to search, and click Find again to jump to the next match.
- Tick any of the options below to narrow the search.
- Click Replace for the highlighted match, or Replace all to change every one.
The four options that change your results
| Option | What it does | When I'd tick it |
|---|---|---|
| Match case | Makes your search case-sensitive | Fixing a name typed in the wrong case |
| Match entire cell contents | Only finds cells that are an exact match | Swapping a status like Done without touching cells that only contain it |
| Search using regular expressions | Finds cells that match a pattern | Reformatting codes, numbers or anything with a shape |
| Also search within formulas | Includes formulas in the search | Renaming a sheet or range that formulas point at |
Match entire cell contents is the one that saves you from accidents. Without it, replacing a short word also changes it inside longer words and phrases. Google's examples of regex searches are run with Match case ticked and Match entire cell contents unticked, so your results can differ depending on what's checked.
Searching with regular expressions
Google products use RE2 for regular expressions, and Google Sheets supports RE2 except Unicode character class matching. Tick Search using regular expressions, type your pattern next to Find and click Find.
- To search for a character that means something in regex, such as the dollar sign, put a backslash in front of it.
- A capture group is a part of the pattern in round brackets. In Replace with, you refer back to it with a dollar sign followed by the group's number.
- \d+ matches a run of digits, which is how Google's REGEXEXTRACT example pulls 241 out of a sentence.
That capture group trick is the one that saves hours. It lets you rearrange text, such as moving a surname in front of a first name, in one Replace all instead of editing each cell.
Replacing text with formulas instead
The dialog changes your data in place. A formula keeps the original and shows the cleaned version next to it, which is safer when you're not sure of the pattern yet.
| Function | Example | What it does |
|---|---|---|
| SUBSTITUTE | =SUBSTITUTE(A2,"new york","New York") | Replaces existing text with new text, every instance unless you name one |
| REPLACE | =REPLACE("Spreadsheets", 1, 6, "Bed") | Replaces a set number of characters from a starting position |
| REGEXREPLACE | =REGEXREPLACE("Spreadsheets", "S.*d", "Bed") | Replaces every part of the text that matches a pattern |
| REGEXMATCH | =REGEXMATCH("Spreadsheets", "S.r") | Returns TRUE or FALSE for whether the text matches |
| REGEXEXTRACT | =REGEXEXTRACT(A2, "\d+") | Pulls out the first part of the text that matches |
| TRIM | =TRIM(A2) | Removes leading, trailing and repeated spaces |
A few details from Google's notes catch people out:
- SUBSTITUTE matches parts of words as well as whole words, so a search for vent also changes the text inside eventual.
- SUBSTITUTE can replace one instance or all of them, using the optional occurrence number, but not several and not all in a single call.
- The regex functions only work with text. Convert numbers first with TEXT, and wrap the result in VALUE if you need a number back.
- REGEXEXTRACT can return several results at once with capture groups, spilling them into separate columns.
Formulas also make it easy to compare lists once they're clean, for example with VLOOKUP in Google Sheets.
Menu tools that clean up faster
Some jobs people use find and replace for have their own tool under the Data menu.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
- Trim whitespace: select the range, then click Data, Data cleanup, Trim whitespace to remove extra leading, trailing or excessive spaces.
- Remove duplicates: click Data, Data cleanup, Remove duplicates, choose the columns and whether there's a header. Cells with identical values but different letter cases, formatting or formulas count as duplicates.
- Split text to columns: click Data, Split text to columns, and pick the separator, so a column like Last name, First name becomes 2 columns.
One limit applies to both TRIM and Trim whitespace: a non-breaking space won't be trimmed. If you'd rather see repeats before you delete them, here's how to highlight duplicates in Google Sheets first.
How I'd handle a big clean-up
For a simple word swap, I'd use Edit, Find and replace with Match entire cell contents ticked, and check a few matches with Find before I hit Replace all. For anything with a pattern, I'd test it in a REGEXREPLACE formula in a spare column first, then run the same pattern in the dialog once I'm happy. And I'd always tick Also search within formulas when I'm renaming something formulas depend on. If you move between apps, here's how find and replace in Excel differs.
Frequently asked questions
Where is find and replace in Google Sheets?
Click Edit, then Find and replace. To search only, press Ctrl + f on Windows or ⌘ + f on a Mac.
Can I use regular expressions in Google Sheets find and replace?
Yes. Tick Search using regular expressions. Google Sheets uses RE2 and supports capture groups in the replacement.
How do I replace text only when the whole cell matches?
Tick Match entire cell contents, which searches only for cells that are an exact match.
Does find and replace change formulas in Google Sheets?
Only if you tick Also search within formulas, which includes formulas in the search.
What formula replaces text in Google Sheets?
SUBSTITUTE replaces existing text with new text, REPLACE swaps characters by position, and REGEXREPLACE replaces text that matches a regular expression.
