You need to swap an old domain for a new one. Or fix a product name across 4,000 rows. Or standardize "Inc" and "Incorporated" into one thing.
Find and Replace from the Edit menu is a manual, one-shot operation. A formula updates automatically. So you reach for SUBSTITUTE.
The Old Way (SUBSTITUTE and REGEXREPLACE)
The basic version is simple enough.
=SUBSTITUTE(A1, "Corp", "Corporation")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.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "Corp", "Corporation"), "corp", "Corporation"), "CORP", "Corporation")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.
=REGEXREPLACE(A1, "10.00", "free")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.
=FITS("In " & A1 & ", replace every spelling of corp with Corporation, ignoring case")Conditional replacements work the same way. No IF nesting required.
=FITS("Replace only the first http:// with https:// in " & A1 & ", leave the rest alone")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.
Stop Nesting SUBSTITUTE Calls
FITS puts plain-English AI formulas inside Google Sheets. Describe the replacement. Get the result. Free tier included.