SUBSTITUTE replaces specific text inside a cell with other text.
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])
Replace text
=SUBSTITUTE(A2, "Ltd", "Limited") changes "Acme Ltd" to "Acme Limited".
Remove a character
Replace it with nothing: =SUBSTITUTE(A2, "-", "") turns 98-76-54 into 987654.
Replace only one occurrence
=SUBSTITUTE(A2, "-", "/", 2) replaces only the second dash.
Several replacements
Nest them: =SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", "") removes both brackets.
Count how many times text appears
=LEN(A2)-LEN(SUBSTITUTE(A2, ",", "")) counts the commas in a cell.
SUBSTITUTE vs REPLACE
- SUBSTITUTE finds text and replaces it.
- REPLACE replaces characters by position, such as the first 3.
SUBSTITUTE is case sensitive: "ltd" will not match "Ltd".
Worked example
A2 holds "98-765-4321". =SUBSTITUTE(A2,"-","") returns 987654321. To replace only the second dash, use =SUBSTITUTE(A2,"-","/",2), which gives 98-765/4321.
Common mistakes
- Expecting SUBSTITUTE to ignore case. It is case sensitive.
- Using REPLACE when you know the text, not the position.
- Forgetting the result is text. Wrap in VALUE for a number.
Frequently asked questions
SUBSTITUTE or REPLACE?
SUBSTITUTE swaps matching text. REPLACE swaps text at a given position and length.
Can I replace several things at once?
Nest them: =SUBSTITUTE(SUBSTITUTE(A2,"-","")," ","").
Does it change the original cell?
No. It returns a new value.
Practice this online, free
Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.
Practice these formulas: SUBSTITUTE, REPLACE, TRIM.
Start Excel practice → Case studies
