How to Replace Text in Google Sheets

How to Replace Text in Google Sheets: Three Methods

There are exactly three ways to replace text in Google Sheets, and picking the wrong one is the source of most of the pain.

1. Find and replace (Ctrl+H, or Cmd+Shift+H on Mac). Edits the cells in place, once. Use it for a one-time cleanup where you never want the change to re-run.
2. SUBSTITUTE. A formula that finds a matching string and swaps it. Lives in a helper column and recalculates whenever the source changes. Use it when new rows keep arriving.
3. REPLACE. A formula that swaps characters by position, not by match. Use it when you know the location, such as replacing the first three digits of every phone number.

The rule of thumb: if the data is final, use Ctrl+H. If the data keeps changing, use a formula. If you know where the text sits rather than what it says, use REPLACE instead of SUBSTITUTE.

The SUBSTITUTE Function in Google Sheets

SUBSTITUTE takes four arguments, and the fourth one is the one most people never learn about.

• text_to_search is the cell or string you are editing.
• search_for is the exact string to find. It is case sensitive and there are no wildcards.
• replace_with is what goes in its place. Pass an empty pair of quotes to delete the match instead of replacing it.
• occurrence_number is optional. Omit it and every match is replaced. Set it to 1 and only the first match changes.

That fourth argument answers a question people usually solve with far uglier formulas. To change only the second slash in a path:

There is no way to count backwards, so "replace the last occurrence" has no direct form. You have to count the total matches first and pass that number.

REPLACE vs SUBSTITUTE: They Are Not the Same Function

Both exist, and picking the wrong one produces silently wrong output rather than an error. SUBSTITUTE works by match. REPLACE works by position.

To mask all but the last four digits of a card number in A1:

REPLACE never looks at the content. If one row has a shorter number, it cuts in the wrong place and gives you a plausible-looking wrong answer. Use REPLACE only when every value is the same fixed width.

Find and Replace: The Four Checkboxes That Matter

Open Edit > Find and replace, or press Ctrl+H. The options below the input boxes change the result significantly, and two of them are genuinely dangerous.

• Match case. Off by default here, unlike the SUBSTITUTE function. This is why the menu tool finds things your formula misses.
• Match entire cell contents. Turn this on to change cells that equal your search term, rather than cells that merely contain it. Without it, searching for "NY" also hits "Sunny" and "Company".
• Search using regular expressions. Enables RE2 patterns in the search box. Note that this also makes ordinary characters like the period and the question mark behave as operators.
• Also search within formulas. The dangerous one. With it on, a replacement can rewrite the text inside your formulas, not just the values they produce. There is no confirmation step. Take a copy of the sheet first.

Find and replace also has no undo across a whole range beyond the single Ctrl+Z, and it cannot be scoped to "only rows where another column says X". That is the point where a formula becomes the right tool.

The Old Way (SUBSTITUTE and REGEXREPLACE)

The basic version is simple enough.

Then you notice half your rows did not change. SUBSTITUTE is strictly case sensitive. It matches "Corp" and ignores "corp" and "CORP". The usual workaround is to nest calls, one per casing.

It also replaces every occurrence by default. Want to change only the first one, or only the last? That means counting instances and passing an occurrence number, which gets messy fast.

REGEXREPLACE handles case with a flag, but adds an escaping trap. Ordinary-looking characters are operators.

That period matches any character. So "10a00" gets replaced too. You have to write "10\.00" to mean an actual dot.

The FITS Way (State the Rule)

With FITS, case handling and scope are just words in the sentence.

Conditional replacements work the same way. No IF nesting required.

The formula says what it does. Six months from now, you will still be able to read it.

When to Use Each

SUBSTITUTE is free and fast for one exact string with one exact casing. Reach for =FITS() when the match is fuzzy, the casing varies, or the rule has exceptions. See also our definitive guide to AI data cleaning, our roundup of AI formulas for content marketers, and the hub guide on automating Google Sheets tasks you used to need regex for. When the job is many swaps instead of one, see bulk replacing text in Google Sheets. When the replacement is nothing at all, see removing a word from an entire column. When you do not have a fixed string to replace, only a set of near-identical variants, see grouping similar text in Google Sheets. When the rows themselves should disappear once the value is right, see how to hide rows in Google Sheets based on a cell value.

Frequently Asked Questions