SUBSTITUTE

Replace specific text inside a string with new text using Excel's SUBSTITUTE function, matching by content rather than position.

Text
|
Excel All versions
|
Google Sheets Supported

Spreadsheet editor

Syntax

=SUBSTITUTE(text, old_text, new_text, [instance_num]) Returns: Text

Arguments

Argument Required Description
text Yes The original text string where substitutions will be made.
old_text Yes The text to be replaced in the original text string.
new_text Yes The text that will replace the old_text.
instance_num No The occurrence number of old_text to be substituted. If omitted, all occurrences will be replaced.

About

SUBSTITUTE finds matching text inside a string and swaps it for new text. You give it the original text, the old text you want to find, and the new text to put in its place, and it returns the updated string. Add an optional instance number to change only one occurrence instead of every match.

Use SUBSTITUTE to clean and reshape text: update an email domain after a rebrand, remove thousands separators before converting to a number, or swap product-code separators. Because it matches on the actual characters, it works no matter where the text sits in the cell. Keep in mind that matching is case-sensitive, so "USA" and "usa" are treated as different.

Reach for SUBSTITUTE when you know the text you want to replace. If you instead know the position (replace 3 characters starting at character 5), use REPLACE. To locate text first, pair it with FIND or SEARCH, and use TRIM to tidy up leftover spaces.

Examples

Watch out for case-sensitive matches

See why "usa" and "USA" behave differently. Edit the search text in the formulas and watch the replacement appear only when the case matches exactly.

Spreadsheet editor

Turn text amounts into real numbers

Nest two SUBSTITUTE calls to strip the "$" and "," from a text amount, then wrap it in VALUE. Change a value and watch the tax column do real math on it.

Spreadsheet editor

Watch out for

⚠

Matching is case-sensitive

SUBSTITUTE only replaces text that matches the exact case of old_text, so searching for "usa" will skip "USA" and the cell comes back unchanged.

→ Match the case carefully, or wrap the text in UPPER or LOWER first so both sides use the same case.

⚠

Confusing it with REPLACE

SUBSTITUTE swaps text by what it says, while REPLACE swaps by position. Reaching for the wrong one leads to characters changing in places you did not expect.

→ Use SUBSTITUTE when you know the text to find, and use REPLACE when you know the start position and length.

⚠

Nothing changes and no error appears

If old_text is not found, SUBSTITUTE returns the original string untouched, which can look like the formula failed.

→ Check for hidden spaces or mismatched characters. Run TRIM on the cell, and confirm the exact spelling and case of old_text.

⚠

Replacing every match by accident

Leave out the instance number and SUBSTITUTE changes all occurrences, which is a problem when only one should change.

→ Add the optional instance_num argument to target a single occurrence, such as the second comma or the first dash.

Tips & notes

You can nest SUBSTITUTE calls to remove or swap several different characters in one formula, for example wrapping one SUBSTITUTE inside another to strip both spaces and dashes. To remove non-printable characters instead of specific text, use CLEAN.

Common questions

Is the SUBSTITUTE function case-sensitive?

Yes. SUBSTITUTE only replaces text that matches the case of old_text exactly, so "World" and "world" are treated as different. If you need to ignore case, convert both with UPPER or LOWER first.

How do I replace just one occurrence instead of all of them?

Add the optional instance_num argument. For example, =SUBSTITUTE(A2, "-", "/", 2) changes only the second dash and leaves the others as they are.

What is the difference between SUBSTITUTE and REPLACE?

SUBSTITUTE finds and swaps text based on its content, so you tell it what to look for. REPLACE works by position, so you tell it where to start and how many characters to change.

What happens if the text I am looking for is not there?

SUBSTITUTE returns the original string unchanged. It does not raise an error, so double-check spelling, case, and stray spaces if a replacement does not happen.