TextLevel: BeginnerAvailable in: Excel 2007+
In this article
- Syntax
- Examples
- Example 1
- Example 2
- Power combo
- Related functions
SUBSTITUTE replaces existing text with new text — all occurrences or just the n-th one.
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])
| Argument |
What it means |
instance_num |
Which occurrence to replace (all if omitted). |
Examples
Example 1
=SUBSTITUTE(A2, "-", "")
Remove all dashes from a phone number.
Example 2
=LEN(A2)-LEN(SUBSTITUTE(A2, ",", ""))+1
Count items in a comma list.
Power combo
=TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", 100)), 100))
Last word of a text in every Excel version.
REPLACE · TRIM · LEN
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
✨ Ask AI about this articleStuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong