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 (documented across all versions; executed in every LibreOffice version 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 (Excel for the web, Sheets & LibreOffice executed; desktop Excel per docs)
Both execute identically in every LibreOffice build and in the Google Sheets run we execute (desktop Excel per Microsoft's docs — we do not run desktop Excel). One cross-app note: Google Sheets' REGEXREPLACE covers both jobs (and more) but is Sheets-only — formulas using it break on export. Excel for the web is a separate application with its own calculation engine, and that one we do execute: all 8 corpus cases for these functions matched the documented values there (recalculated on OneDrive, 2026-09-01); the per-case values are on the individual function pages.
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.