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").
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
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.