The SUBSTITUTE guide
=SUBSTITUTE(B2, "Helpdesk", "Service Desk") swaps every Helpdesk in B2 for Service Desk. =SUBSTITUTE(D2, "-", "") swaps each hyphen for nothing, which removes it.
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])
- text: the cell to change.
- old_text: what to find. Capitals count: "helpdesk" doesn't find "Helpdesk".
- new_text: what to put in its place, exactly as you type it.
- instance_num: which copy to swap. Leave it out to swap every copy.
Text that doesn't contain old_text comes through unchanged, so one formula works down the whole column.
Removing characters
Swap a character for empty quotes and it's gone: SUBSTITUTE(D2, "-", "") turns 0113-496-0731 into 01134960731. Put a space between the quotes and you get spaces instead. The result is text, so the leading 0 stays; VALUE would drop it. To remove two different characters, put one SUBSTITUTE inside another: SUBSTITUTE(SUBSTITUTE(D2, "-", ""), " ", "").
SUBSTITUTE or REPLACE?
SUBSTITUTE swaps text by what it says. REPLACE(text, start, count, new) swaps characters by where they are. When the word moves around, as Helpdesk does in "Senior Helpdesk Analyst", only SUBSTITUTE finds it every time. REPLACE is for fixed positions, such as masking the first digits of an account number.
Common mistakes
- Old and new the wrong way round. SUBSTITUTE looks for "Service Desk", doesn't find it, and nothing changes.
- The wrong capitals in old_text. Nothing changes, with no error.
- A space instead of nothing.
" "leaves gaps where the hyphens were. - An instance number you didn't mean. With 1, only the first hyphen goes.
SUBSTITUTE as one step of a clean-up: clean a bank description. For spaces at the ends of text: TRIM.
FAQ
- How do I replace a word in Excel with a formula?
- Use SUBSTITUTE: =SUBSTITUTE(B2,"Helpdesk","Service Desk") swaps every Helpdesk in B2.
- How do I remove a character from a cell in Excel?
- Swap it for empty quotes: =SUBSTITUTE(A2,"-","") removes every hyphen.
- Is SUBSTITUTE case-sensitive?
- Yes. "helpdesk" doesn't find "Helpdesk", and nothing changes.
- What is the difference between SUBSTITUTE and REPLACE?
- SUBSTITUTE swaps text by what it says, wherever it is. REPLACE swaps characters at a position you give it.



































































