How to Use SUBSTITUTE Function in Excel + Tips

Hello everyone! Today i will show you how to use SUBSTITUTE function in Excel step by step.

What is SUBSTITUTE in Excel?

The SUBSTITUTE function help you replace old text with new text inside any cell or text string. It is super useful when you want to clean data, fix mistake, remove dash or space, or change word fast without edit each cell manually!

🛠️ Formula Syntax

=SUBSTITUTE(text, old_text, new_text, [instance_num])

• text: The cell or text string where you want replace text (example: A1).
• old_text: The text you want to remove or change (example: " " or "Excel").
• new_text: The new text you want to put in (example: "-" or "Word").
• [instance_num] (Optional): Which position of old text you want to replace. If you leave blank, Excel will replace all occurrences of old text in cell!

📊 Simple Examples

Example 1: Replace all spaces with dash -

=SUBSTITUTE(A1, " ", "-")

This formula find all space " " in cell A1 and change them to -.

Example 2: Replace word "Excel" with "Word"

=SUBSTITUTE(A1, "Excel", "Word")

This formula replace existing word "Excel" with "Word".

Example 3: Replace all dash - with slash /

=SUBSTITUTE(A1, "-", "/")

This formula convert date format or text with dash - to slash /.

Example 4: Replace only the 2nd occurrence of "a"

=SUBSTITUTE(A1, "a", "A", 2)

This formula replace only the 2nd time letter "a" appears in cell A1.

Example 5: Remove dash or space completely (Replace with empty "")

=SUBSTITUTE(A1, "-", "")

This formula remove all dash - from cell A1. For example, phone number 012-345-678 become 012345678.

Example 6: Replace multiple different characters (Nested SUBSTITUTE)

=SUBSTITUTE(SUBSTITUTE(A1, "(", ""), ")", "")

You can put one SUBSTITUTE inside another SUBSTITUTE to remove both ( and ) from text at the same time!

Example 7: Count how many times a character appears in cell (Pro Trick)

=LEN(A1) - LEN(SUBSTITUTE(A1, "a", ""))

This trick compare original text length with length after removing "a". The answer tell you total count of letter "a" in cell A1!

💡 Quick Tip:

1. SUBSTITUTE is case-sensitive! "excel" and "Excel" are not same. If you search "excel", Excel will not change "Excel".
2. SUBSTITUTE only replace text value, not cell color, font, or cell formatting!