Find and Replace in Google Sheets (Regex, $1 and Formulas)

Find and replace swaps one piece of text for another everywhere it appears, all at once. It's how you fix a misspelled name in 200 rows, change "St" to "Street", or clean up imported data, without editing each cell by hand.

Open Find and replace

Press Ctrl + H (Windows) or ⌘ + Shift + H (Mac). You can also go to Edit → Find and replace.

Google Sheets Find and replace dialog with Find (\w+), (\w+), Replace with $2 $1, Search Specific range Sheet1!F2:F4, Match case and Search using regular expressions ticked — The real dialog, set up to swap "Last, First" names in a selected range.

Replace step by step

Type what you're looking for in Find.
Type the new text in Replace with. Leave it empty to delete the found text.
Set Search to All sheets, This sheet or Specific range.
Click Replace all to change every match at once, or Find and then Replace to review one at a time.

The options explained

Option	What it does
Match case	"cat" won't match "Cat". Leave off to ignore capitalization.
Match entire cell contents	Only replaces cells that contain exactly the search text, nothing more.
Search using regular expressions	Lets you search with patterns (see below).
Also search within formulas (formulae in UK English)	Looks inside formulas, not just their results. Useful for changing a range in many formulas.

Handy regular-expression examples

Tick Search using regular expressions first, then:

Find	Matches
`\s+$`	Trailing spaces at the end of a cell
`^\s+`	Spaces at the start of a cell
`\d+`	Any run of digits
`-\|/`	A hyphen or a slash
`\s$.?$`	Anything in brackets, plus the space before it, like " (copy)"
`^.*@`	Everything up to and including the @ of an email address
`.*`	Any text at all. Find has no `` wildcard, so use `.` with regular expressions on.

Careful with Replace all: it can't be undone selectively. Press Ctrl + Z right away if the result isn't what you expected, or narrow the search to a range first.

Tip: to work on one column only, select it before opening Find and replace. Search is then set to Specific range with your selection.

Rearrange text with $1 and $2

With regular expressions on, parts of the search in brackets can be reused in Replace with as $1, $2 and so on. For example, to turn "Smith, Anna" into "Anna Smith":

Find	Replace with	Result
`^(.+), (.+)$`	`$2 $1`	Smith, Anna → Anna Smith
`^(\d{2})/(\d{2})/(\d{4})$`	`$3-$2-$1`	31/12/2026 → 2026-12-31

Tested: on three names in F2:F4 (Smith, John, Garcia, Ana, Lee, Kim), we used (\w+), (\w+) and $2 $1 with Search: Specific range. Sheets reported "Replaced 3 instances of (\w+), (\w+) with $2 $1", and every name was swapped:

Names column after the replacement: John Smith, Ana Garcia, Kim Lee — After Replace all: First Last.

When we ticked Search using regular expressions, Match case was ticked too. Keep that in mind if your pattern uses lowercase and uppercase letters.

The second one only works on dates stored as text. Real dates are numbers, so change how they look with Format → Number instead.

Replace inside formulas

Tick Also search within formulas to edit many formulas at once. Typical uses:

A tab was replaced by a new one: find Data2025!, replace with Data2026!.
Formulas use a range that's too short: find B2:B100, replace with B2:B.
Formulas copied from a website have curly quotes: find “ and ” and replace each with ". See Formula parse error.

To find every broken formula, search for #REF! with this option on. See how to fix #REF!.

Replace with a formula instead

Find and replace changes your data for good. To keep the original and show a cleaned-up copy next to it, use a formula:

=SUBSTITUTE(A2, "St", "Street")
=REGEXREPLACE(A2, "\s*\(.*?\)", "")

SUBSTITUTE swaps plain text; REGEXREPLACE accepts the same patterns as the dialog. Both update when the original changes. For spaces specifically, TRIM is simpler, and to change capitalization use UPPER, LOWER and PROPER.

Just finding (not replacing)

To only search, press Ctrl + F. A small box appears; type your text and use the arrows to jump between matches.

Frequently asked questions

How do I remove all spaces from a column?

For extra spaces at the start and end, use Data → Data cleanup → Trim whitespace. To remove every space, open Find and replace, put a single space in Find, leave Replace with empty, and click Replace all.

Can I replace line breaks?

Yes, with regular expressions on: search for \n and replace with a space.

Why does "Replace all" say 0 changes?

Check Match case and the Search scope. Extra spaces or a wrong sheet selection are the usual causes.

Can I find and replace in several files at once?

No. Find and replace works inside one spreadsheet at a time, but All sheets covers every tab in that file.

How do I split a list into lines inside the cell?

Use a formula such as =SUBSTITUTE(A2, ", ", CHAR(10)) to turn each comma into a line break, and turn on text wrapping so the breaks show.