How to Find and Replace Multiple Values in Google Sheets

How to Find and Replace Multiple Values in Google Sheets

Open Find and replace with Ctrl+H, or Command+Shift+H on a Mac, or from Edit then Find and replace. The dialog runs one pair per click of Replace all. To do many pairs in a single pass you need one of three things: regex alternation when every term maps to the same replacement, a mapping table looped with REDUCE when each term maps to something different, or a script when the same list runs again next month.

Jump to the part that matches your problem:

• Many terms, one replacement, no helper column: use REGEXREPLACE with pipes.
• Each term has its own replacement: loop a two-column mapping table.
• Your lookup only works when the cell matches exactly: that is a VLOOKUP limit, not a typo.
• The replacement has to hit every tab: set the Search dropdown to All sheets.
• Your formulas became plain text after a replace: the formulas checkbox was off.
• You need to replace line breaks or invisible spaces: those are CHAR(10) and CHAR(160).
• You replaced the wrong thing: undo is one step, and version history is the backstop.

How to Replace Multiple Words at Once With One Formula

When a long list of terms all collapse to the same replacement, alternation does it in one call. Separate the terms with pipes inside REGEXREPLACE.

Order matters inside the pipes. RE2 takes the leftmost match it can make, so put the longest variant first. With Corp|Corporation in that order, the word Corporation gets its first four letters replaced and you are left with a stray ending.

Add (?i) at the front of the pattern to make the whole match case insensitive. That is the one line that removes the need for a separate nested call per casing variant.

Wrap it in ARRAYFORMULA to run the whole column at once, and guard the blanks so empty rows stay empty.

How to Replace Each Term With a Different Value From a Mapping Table

Alternation only works when every term becomes the same thing. Real rebrands are not like that. Acme Corp becomes Acme, Globex Inc becomes Globex, Initech LLC becomes Initech. Put the find terms in column D and the replacements in column E, then loop the pairs.

REDUCE starts with the original cell, then feeds the result of each replacement into the next one. Twenty pairs is twenty rows in the table, not twenty nested calls in the formula.

The reason the formula loops over SEQUENCE rather than the range itself is the part that catches people out. Passing a two-column range into REDUCE does not hand your lambda a pair. REDUCE flattens the range and iterates one cell at a time in row-major order, so it would visit D2, then E2, then D3. Indexing by row number is what keeps each find term attached to its own replacement.

SUBSTITUTE is case sensitive, so a mapping table needs the exact casing that appears in your data. If casing varies, swap SUBSTITUTE for REGEXREPLACE with the (?i) prefix inside the same REDUCE.

Why VLOOKUP Cannot Replace a Term Inside a Sentence

The most common mapping-table attempt is a lookup against the pair list.

It works, but only when the entire cell equals the search term. VLOOKUP matches a whole value, so it cannot touch a term sitting inside a longer sentence, and it misses anything with different casing or a trailing period. That is why a vendor column cleans up fine and a description column does not. Use the REDUCE pattern above for anything embedded in prose.

Why Nested SUBSTITUTE Breaks Down Past Three Pairs

SUBSTITUTE handles one pair. For a second pair, you wrap the first call. For a third, you wrap again.

That is three swaps of one term, and it is already hard to read. A real list has twenty terms. Every new term means opening the formula, counting parentheses, and hoping the person after you does the same.

SUBSTITUTE takes an optional fourth argument, the occurrence number, which replaces only the nth instance instead of all of them. =SUBSTITUTE(A2, "-", " ", 2) changes the second hyphen and leaves the rest alone. That is useful for a single surgical edit, and it is no help at all for a long pair list.

Nesting is fine at two or three pairs. Past that, move to the mapping table above, to a script, or to one plain-English formula.

How to Run Every Replacement Pair in One =FITS() Formula

With FITS, you list the replacements once in plain English and let the formula apply all of them.

Every pair runs in one pass. Casing variants are handled without a separate nested call for each one. The rest of the sentence comes back untouched.

If your replacement list already lives in the sheet, point the formula at it instead of typing the pairs.

Adding a twenty-first term now means adding a row, not rewriting the formula.

How to Find and Replace Across All Sheets at Once

In the Find and replace dialog, the Search dropdown defaults to this sheet. Change it to All sheets and one Replace all covers every tab in the file. The third option, Specific range, is the safe middle ground when only one column should change.

Two habits stop a whole-file replace from going wrong. First, leave Also search within formulas unticked unless you actually intend to rewrite formula text, because that option matches sheet names and function arguments too. Renaming a tab reference inside a formula is how a cross-sheet replace turns into a file full of errors.

Second, name the current version before you run it. File, then Version history, then Name current version. It costs five seconds and it gives you a labelled point to roll back to.

Why Find and Replace Turned Your Formulas Into Text

This is the single most expensive surprise in a bulk replace, and it is a consequence of the checkbox most people leave alone.

With Also search within formulas unticked, Sheets matches the displayed result of a cell rather than the formula behind it. If a formula cell displays a matching string and you run Replace all, Sheets writes the replaced string into the cell. The formula is not edited. It is overwritten with a static value, and the calculation is gone.

The defence is to scope the replace. Select the range first and set Search to Specific range, or press Ctrl+~ to switch to formula view and see exactly which cells in the target range are calculated before you commit.

How to Bulk Replace Using Regular Expressions

Tick Search using regular expressions and the dialog gains real pattern matching. Google Sheets uses the RE2 engine, in the dialog and in REGEXREPLACE alike. Three details decide whether your pattern works.

1. Capture groups are written as $1 in the Replace with field, not as backslash-one. To keep a captured chunk and drop the rest, find SKU-(\d+)-OLD and replace with SKU-$1. A literal dollar sign in the output is escaped as $$.
2. RE2 has no lookahead and no lookbehind, and no backreferences inside the pattern itself. Patterns copied from a Python or JavaScript answer often use them, which is why an otherwise correct expression fails here.
3. When the pattern is invalid, REGEXREPLACE returns #VALUE! with the message that parameter 2 is not a valid regular expression. That error text means the syntax is unsupported, not that your data is wrong.

Escape the characters that RE2 treats as operators when you want them literally: $ . * + ? ( ) [ ] ^ \. Searching for a price is \$10\.00, not $10.00.

How to Bulk Replace Line Breaks and Invisible Characters

Pasted text arrives carrying characters you cannot see. Two of them cause most of the trouble.

Line breaks are CHAR(10). In the dialog, tick the regex box and search for \n. In a formula, use =SUBSTITUTE(A2, CHAR(10), " "). CLEAN also strips them, along with every other control character from code 0 to 31 plus code 127.

Non-breaking spaces are CHAR(160), and they are the reason a cell that looks trimmed still fails a lookup. Code 160 sits outside the control range, so CLEAN leaves it, and it is not the space character, so TRIM leaves it too. Replace it explicitly.

That one formula clears control characters, converts non-breaking spaces to real ones, then collapses the result. It is the standard first pass on any imported column, and it pairs with removing the extra spaces a bulk edit leaves behind.

How to Bulk Replace With Apps Script

When the same pair list runs every month, put it in a sheet and let a script walk it. createTextFinder is the scripted version of the dialog, with the same options exposed as methods.

function bulkReplace() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const target = ss.getSheetByName('Data');
  const pairs = ss.getSheetByName('Map').getRange('D2:E21').getValues();
  let total = 0;
  pairs.forEach(([find, replace]) => {
    if (find === '') return;
    total += target.createTextFinder(String(find))
      .matchCase(true)
      .matchEntireCell(false)
      .replaceAllWith(String(replace));
  });
  SpreadsheetApp.getUi().alert(total + ' replacements made');
}

replaceAllWith returns the number of occurrences it changed, which is why the loop can report a total. Add .useRegularExpression(true) if the find column holds patterns rather than plain strings. This route is worth the setup only when the list is stable and recurring, since every run edits the file with no preview.

How to Undo a Bulk Find and Replace

A Replace all is one atomic step in the edit history, even when it changed a thousand cells across every tab. Ctrl+Z immediately afterwards reverts the whole operation, not one cell of it. There is no undo button inside the dialog itself, so close it first.

Once the session has moved on, or the tab has been closed, undo is gone and version history is the recovery path. Open File, then Version history, then See version history, or press Ctrl+Alt+Shift+H (Command+Option+Shift+H on Mac). Pick the timestamp before the replace and restore it.

A script-driven replace is worse to unwind, because each replaceAllWith call is its own edit. Name a version before running one.

When to Use Each

For one exact swap on clean data, the dialog or SUBSTITUTE is free and instant. Use REGEXREPLACE with pipes when many terms collapse to one value, and the REDUCE mapping table when each term has its own replacement. Reach for =FITS() when the pairs are fuzzy rather than exact, when casing and punctuation vary in ways a pattern would have to enumerate, or when the terms sit inside longer prose. Related jobs use the same pattern: replacing one term case-insensitively, removing a word from an entire column, and standardizing date formats after an import. The full tour is in automating Google Sheets tasks you used to need regex for.

Frequently Asked Questions