SUBSTITUTE in Excel: Replace Specific Text in Strings

Substitutes new text for old text in a text string. Great for data cleaning.

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

Learning Resources

Want to master Excel? Check out this Top-Rated Course.

How to Use SUBSTITUTE in Excel

The SUBSTITUTE function replaces existing text with new text in a text string. It is case-sensitive.

Syntax

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

Example: Cleaning Phone Numbers

To strip dashes from a phone number: =SUBSTITUTE(A2, "-", "")

Common Errors & Fixes

  • SUBSTITUTE is not changing anything

    Causes:
    • old_text does not match due to case sensitivity or extra spaces.
    • old_text may have leading/trailing spaces that are not visible.
    Fixes:
    • Use TRIM(A1) on the source cell to remove extra spaces.
    • Double-check exact spelling and case of old_text.

Frequently Asked Questions

Is SUBSTITUTE case-sensitive?

Yes, SUBSTITUTE is case-sensitive. "apple" will not match "Apple". For case-insensitive replacement, use REPLACE with UPPER/LOWER.

Can I replace only the first occurrence?

Yes, add a fourth argument (instance_num) to specify which occurrence. Omit it to replace all occurrences.

What is the difference between SUBSTITUTE and REPLACE?

SUBSTITUTE replaces specific text wherever it appears. REPLACE replaces text at a specific starting position with a given length.

Related Formulas

Explore related formula generators to solve similar problems

CONCATENATETRIMUPPERLOWERPROPER

🛠️ Related Tools

Formula Comparator

Compare Excel formulas side by side

Remove Duplicates

Clean duplicate values from your data

GPA Calculator

Weighted & unweighted grade point average

SUMIFS Across Sheets

Sum across multiple Excel sheets

Want to become an Excel Pro?

Stop searching for formulas. Master Excel in 30 days with this top-rated course.

Learn More