Text · replace by content · Nov 2026

The SUBSTITUTE function,
measured & explained.

Syntax, a live playground, a measured replacement gallery, the case-sensitivity and instance_num rules, and a sourced error library: swapping text by content.

D1
fx
=SUBSTITUTE(A2, "e", "E")
0
measured gallery rows
0
sourced errors fixed
0
platforms checked
// 01 — Anatomy

How SUBSTITUTE works

Replaces old_text with new_text in a string. Case-sensitive; replaces every occurrence unless instance_num targets one.

=SUBSTITUTE(text, old_text, new_text, [instance_num])
textThe string to edit.
old_textThe content to find (case-sensitive).
new_textThe replacement; empty removes it.
instance_numOptional: which occurrence to replace.
.bool-moat td.res-text{color:var(--cyan);font-weight:700;font-family:var(--mono)}
// 02 — Replace text

What SUBSTITUTE actually swaps

SUBSTITUTE replaces old_text with new_text — every occurrence by default, or just the nth via instance_num. It is case-sensitive. Every row was measured live in HyperFormula. Click any row to load it.

Replacement gallery — case-sensitive, all instances by default

ExpressionResultWhy
=SUBSTITUTE(A2,"e","E")ExcElReplaces every lowercase e with E
=SUBSTITUTE("aaa","a","b")bbbAll occurrences are replaced by default
=SUBSTITUTE("aaa","a","b",2)abainstance_num 2 replaces only the second a
=SUBSTITUTE("Hello","l","L")HeLLoBoth l characters are replaced
=SUBSTITUTE(A2,"z","")ExcelNo match: the text is returned unchanged
=SUBSTITUTE("abc","ABC","x")abcCase-sensitive: ABC does not match abc
=LEN(A2)-LEN(SUBSTITUTE(A2,"e",""))1Count occurrences by length difference
=SUBSTITUTE("a-b-c","-","/")a/b/cSwap a delimiter character

More replacements

ExpressionResultWhy
=SUBSTITUTE(A3,"o","0")F0rmulaSwap a letter for a digit
=SUBSTITUTE(A2,"Excel","X")XReplace a whole substring
⚠ SUBSTITUTE is case-sensitive and replaces every occurrence unless you pass instance_num. It has no wildcards. To replace by position instead of by content, use REPLACE. The length-difference trick — LEN(text)-LEN(SUBSTITUTE(text,char,"")) — counts how many times a character appears.
// 03 — Try it live

Interactive SUBSTITUTE playground

Edit the table or type any formula — the engine evaluates instantly. Load a preset to see SUBSTITUTE swap case, replace all or one occurrence, leave non-matches unchanged, and count characters.

A1Sample data — click any cell to edit, the formula re-evaluates live
Result
—
✓ readyHyperFormula 3.5
// 04 — Error library

The real-world error library

8 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.

// 05 — Cross-platform

Where SUBSTITUTE works

SUBSTITUTE is available across modern Excel, Google Sheets and LibreOffice. It has been in Excel since 2003.

PlatformSupportNotesGotcha
✓ Supported full SUBSTITUTE◐ Partial works with caveatsCompiled from public docs · verify against your build
// Go deeper

Master text replacement

SUBSTITUTE is one piece. The full course walks the toolkit end to end with live, measured examples.

  • SUBSTITUTE vs REPLACE — content vs position
  • Case-sensitivity and instance_num
  • The length-difference counting trick
  • Removing characters with empty new_text
Free while in beta
Browse the function library

No account needed. Every page has a live playground.

// 07 — FAQ

Frequently asked