Text
Advanced Find & Replace
Perform advanced find and replace operations using SUBSTITUTE. Unlike Edit > Find & Replace, this creates a new column preserving the original data.
Excel Formula
=SUBSTITUTE(A2, "old", "new")Step-by-Step Explanation
1
SUBSTITUTE replaces occurrences of a text string
2
A2 is the cell containing the text
3
"old" is the text to find
4
"new" is the replacement text
5
Replaces ALL occurrences by default
Example
| Original (A) | Result (B) |
|---|---|
| Hello World | Hello Universe |
| 2025-01-15 | 01/15/2025 |
| Mr. Smith | Dr. Smith |
Result: =SUBSTITUTE(A2,"World","Universe") → Hello Universe
Common Variations
Replace nth occurrence
=SUBSTITUTE(A2,"old","new",2)Replace only the 2nd occurrence
Multiple replacements
=SUBSTITUTE(SUBSTITUTE(A2,"a","x"),"b","y")Chain multiple replacements
Case-insensitive (Sheets)
=REGEXREPLACE(A2,"(?i)old","new")Google Sheets regex replace
Need a Custom Version?
Use our AI generator to create a formula tailored to your specific data and requirements.
Try It with AI →