Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
SUBSTITUTE vs REPLACE in Excel and Sheets
Excel & Google Sheets
=SUBSTITUTE(A2,"old","new")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Original Text | Result |
| old product | new product |
| hello old world | hello new world |
| old records only | new records only |
| old and new old | new and new new |
| standard old item | standard new item |
=SUBSTITUTE(A2,"old","new")→new product
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Use SUBSTITUTE when you know what text to swap: =SUBSTITUTE(A2, "old text", "new text") — it replaces every occurrence by default.
- 2To replace only the Nth occurrence, add a fourth argument: =SUBSTITUTE(A2, "the", "a", 2) replaces only the second 'the'.
- 3Use REPLACE when you know the position: =REPLACE(A2, start_num, num_chars, new_text) — for example =REPLACE(A2,1,3,"XXX") replaces the first three characters.
- 4To delete characters, use SUBSTITUTE with an empty string: =SUBSTITUTE(A2," ","") removes all spaces.
Tips
- Chain SUBSTITUTE calls to replace multiple different strings: =SUBSTITUTE(SUBSTITUTE(A2,"foo","bar"),"baz","qux").
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Is SUBSTITUTE case-sensitive?
Yes, SUBSTITUTE is case-sensitive. 'Hello' and 'hello' are treated as different strings. Use LOWER or UPPER first if you need case-insensitive replacement.
When would I choose REPLACE over SUBSTITUTE?
Use REPLACE when the characters to remove vary in content but always occupy the same position — for example, stripping a fixed 3-character country code from the start of a product code.