SUBSTITUTE vs REPLACE: by text or by position
They sound like synonyms; they're opposites in how they aim. SUBSTITUTE finds TEXT you know ("replace every dash") — REPLACE overwrites a POSITION you know ("replace characters 4-6, whatever they are"). Picking the wrong one is why the other one's arguments never seem to make sense.
The differences at a glance
| SUBSTITUTE | REPLACE | |
|---|---|---|
| You specify | The old text and its replacement | Start position, length, and the new text |
| Occurrences | All by default; the 4th argument picks the nth | Exactly one position |
| Case sensitivity | Case-sensitive | N/A — position-based |
| Typical jobs | Strip symbols, swap separators, clean imports | Mask digits, fix fixed-width codes, insert into IDs |
| Compatibility | Universal | Universal (every version of all three apps we test) |
Which should you use?
- SUBSTITUTE — You know WHAT to change: remove commas, change "/" to "-", swap CHAR(10) line breaks. If the text moves around, SUBSTITUTE finds it anyway.
- REPLACE — You know WHERE to change: overwrite characters 5-8 of a fixed-format code, mask a card number (=REPLACE(A2,1,12,"************")), or insert text at a position (length 0).
Compatibility (from executed tests)
Both execute identically in every version of Excel, Google Sheets, and LibreOffice we test. One cross-app note: Google Sheets' REGEXREPLACE covers both jobs (and more) but is Sheets-only — formulas using it break on export.
Example formulas
| SUBSTITUTE (by text) | =SUBSTITUTE(A2,"-","/") |
| REPLACE (by position) | =REPLACE(A2,1,12,"************") |
| Insert without deleting (length 0) | =REPLACE(A2,4,0,"-") |
Full per-version details on each function page: SUBSTITUTE · REPLACE.