Excel SUBSTITUTE Function — Find and Replace in Formulas

What does it do?

SUBSTITUTE replaces occurrences of a specified text string within a larger string. Unlike Find & Replace which changes cells permanently, SUBSTITUTE does it inside a formula — non-destructively, dynamically, and on the fly.

Syntax

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

Argument	Description
=SUBSTITUTE(text, old_text, new_text, [instance_num])
text required	The string to search in.
old_text required	The text to find and replace.
new_text required	The replacement text. Use "" to delete the old_text entirely.
[instance_num] optional	Which occurrence to replace. Omit to replace all occurrences.

Real examples

Example 1

Remove all spaces

=SUBSTITUTE(A2, " ", "")

Replaces every space with nothing — different from TRIM which keeps single spaces between words.

Example 2

Remove hyphens from a code

=SUBSTITUTE(A2, "-", "")

Turns "INV-2026-001" into "INV2026001".

Example 3

Replace a word

=SUBSTITUTE(A2, "Ltd", "Limited")

Updates all instances of "Ltd" to "Limited" in a company name.

Example 4

Replace only the second occurrence

=SUBSTITUTE(A2, "-", "/", 2)

The instance_num 2 replaces only the second hyphen.

Example 5

Count occurrences of a character

=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))

Count commas: original length minus length without commas.

FAQ

Is SUBSTITUTE case sensitive?

Yes — SUBSTITUTE("Hello","hello","Hi") returns "Hello" unchanged. Use LOWER or UPPER on both arguments if you want case-insensitive replacement.

What is the difference between SUBSTITUTE and REPLACE?

SUBSTITUTE finds specific text and replaces it. REPLACE replaces a specific position and length in the string — useful when you know the location but not the content.

Can I chain SUBSTITUTE formulas?

Yes — =SUBSTITUTE(SUBSTITUTE(A2,"Ltd","Limited"),","," and ") applies two replacements in sequence.

SUBSTITUTE for data standardisation

SUBSTITUTE shines when you have data from multiple sources that uses different conventions for the same thing. One system uses "Ltd", another uses "Limited", a third uses "LTD". SUBSTITUTE standardises all of them: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"LTD","Ltd"),"LIMITED","Ltd"),"limited","Ltd"). Chain SUBSTITUTE formulas to handle all variations.

Another powerful use is removing characters that should not exist in a value — like removing all spaces from a phone number to get a consistent dialable format: =SUBSTITUTE(A2," ",""). Or removing all hyphens from product codes before comparison: =SUBSTITUTE(A2,"-","").

SUBSTITUTE is also how you count occurrences of a character. =LEN(A2)-LEN(SUBSTITUTE(A2,",","")) counts the number of commas — which tells you how many items are in a comma-separated list. Divide by the character length of what you're searching for if it is more than one character.

Practise this formula live

ExcelPro has exercises covering this formula across multiple tracks. Free to start.

Try exercises →

Related formulas

TRIM LEN LEFT FIND TEXT TEXTJOIN

The Excel SUBSTITUTE Functionexplained simply