Syntax, a live playground, a measured replacement gallery, the case-sensitivity and instance_num rules, and a sourced error library: swapping text by content.
Replaces old_text with new_text in a string. Case-sensitive; replaces every occurrence unless instance_num targets one.
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.
| Expression | Result | Why |
|---|---|---|
| =SUBSTITUTE(A2,"e","E") | ExcEl | Replaces every lowercase e with E |
| =SUBSTITUTE("aaa","a","b") | bbb | All occurrences are replaced by default |
| =SUBSTITUTE("aaa","a","b",2) | aba | instance_num 2 replaces only the second a |
| =SUBSTITUTE("Hello","l","L") | HeLLo | Both l characters are replaced |
| =SUBSTITUTE(A2,"z","") | Excel | No match: the text is returned unchanged |
| =SUBSTITUTE("abc","ABC","x") | abc | Case-sensitive: ABC does not match abc |
| =LEN(A2)-LEN(SUBSTITUTE(A2,"e","")) | 1 | Count occurrences by length difference |
| =SUBSTITUTE("a-b-c","-","/") | a/b/c | Swap a delimiter character |
| Expression | Result | Why |
|---|---|---|
| =SUBSTITUTE(A3,"o","0") | F0rmula | Swap a letter for a digit |
| =SUBSTITUTE(A2,"Excel","X") | X | Replace a whole substring |
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.
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.
SUBSTITUTE is available across modern Excel, Google Sheets and LibreOffice. It has been in Excel since 2003.
| Platform | Support | Notes | Gotcha |
|---|
SUBSTITUTE is one piece. The full course walks the toolkit end to end with live, measured examples.