SUBSTITUTE vs REPLACE in Excel

SUBSTITUTE vs REPLACE in Excel is a question of what versus where. SUBSTITUTE changes text you name, such as every dot in a phone number, wherever it appears. REPLACE changes a number of characters at a position you give, such as the first three characters of a code, whatever they are. This guide is for anyone cleaning exported data, product codes or phone numbers who wants to pick the right function first time.

SUBSTITUTE vs REPLACE at a glance#

SUBSTITUTE searches by content; REPLACE counts characters.

Watch the argument order. Both have four arguments, but SUBSTITUTE puts the new text third and REPLACE puts it last.

How SUBSTITUTE works#

SUBSTITUTE finds every copy of old_text inside text and swaps it for new_text:

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

• text: the cell to change.
• old_text: what to find, in quotes. It is case-sensitive.
• new_text: what to put in its place. Use "" to delete.
• instance_num: optional. Change only the 1st, 2nd… copy instead of all of them.

If old_text isn't there, SUBSTITUTE returns the text unchanged, with no error. Our guide to cleaning messy data with TRIM, CLEAN and PROPER shows SUBSTITUTE alongside the other cleaning functions.

How REPLACE works#

REPLACE ignores the content and works by position:

=REPLACE(old_text, start_num, num_chars, new_text)

• old_text: the cell to change.
• start_num: the position of the first character to replace (1 is the first character).
• num_chars: how many characters to remove. Use 0 to insert without removing.
• new_text: what to put in their place.

REPLACE is reliable when every value has the same layout, such as codes that always start with a 3-letter prefix. When the position varies, pair it with FIND or SEARCH to locate the spot; our comparison of FIND vs SEARCH explains which to use.

SUBSTITUTE and REPLACE side by side#

Here are common cleaning tasks, each done the easier way and, where it helps, the harder way:

Look at the first two rows. When you know what to change, SUBSTITUTE is shorter and doesn't care where the dots are. Now the prefix rows: both work on "OLD-4471", but SUBSTITUTE would also change "OLD" anywhere else in the text, such as a code like "OLD-GOLD". REPLACE only touches positions 1 to 3.

Case sensitivity in action#

SUBSTITUTE matches upper and lower case exactly:

=SUBSTITUTE("Excel excel","excel","Sheets")

The result is Excel Sheets: only the lowercase "excel" changed. If case shouldn't matter, use LOWER or UPPER first, or use Find and Replace, which ignores case unless you tick Match case.

Nesting SUBSTITUTE for several characters#

One SUBSTITUTE changes one piece of text. To change two different characters, put one SUBSTITUTE inside another. Excel works out the inner one first, then passes its result to the outer one:

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

On a phone number such as 555.201 3344, which mixes a dot and a space, the inner SUBSTITUTE turns the dot into a space, then the outer one turns every space into a dash. The result is 555-201-3344. You can nest as many as you need, but beyond three or four, consider fixing the data once with Find and Replace instead.

SUBSTITUTE, REPLACE or Find and Replace?#

There is a third option that isn't a function at all. Find and Replace (Ctrl+H on Windows, ⌃H on a Mac) changes the cells themselves, across a selection or a whole sheet, and ignores case unless you tick Match case. Use it for a one-off fix to data you own. Use SUBSTITUTE or REPLACE when you need to keep the original values, or when new raw data will keep arriving and the formula should clean it every time. Our guide to Find and Replace tips covers the tool, and SUBSTITUTE and REPLACE in Excel has more formula examples.

When to use SUBSTITUTE#

Use SUBSTITUTE whenever you can name the text to change. It is the better choice when:

• You swap separators, such as dots or spaces to dashes, or semicolons to commas.
• You remove unwanted characters, such as =SUBSTITUTE(A2,"$","") before converting to a number.
• You fix a repeated word or typo across many cells.
• You count occurrences: =LEN(A2)-LEN(SUBSTITUTE(A2,",","")) counts the commas.
• You replace one specific copy with instance_num.

When to use REPLACE#

Use REPLACE when you know the position, whatever the characters are. It is the better choice when:

• Codes have a fixed layout, such as a 3-letter prefix or a 2-digit year at a known place.
• You mask part of a value, such as all but the last four digits of a phone number.
• You insert characters at a position, such as a dash after the area code.
• You strip a variable-length start with FIND: =REPLACE(A2,1,FIND("-",A2),"").

Cleaned codes matter most when you use them as lookup keys. A code with an old prefix or a stray character won't match in a VLOOKUP or XLOOKUP; our comparisons of VLOOKUP vs XLOOKUP and VLOOKUP vs INDEX MATCH cover the lookup side.

Common mistakes with SUBSTITUTE and REPLACE#

These slips cause most problems:

1. Mixing up the argument order. SUBSTITUTE is (text, old, new). REPLACE is (text, start, count, new).
2. Expecting SUBSTITUTE to ignore case. "Ltd" and "LTD" are different to it.
3. Using REPLACE on values with different lengths. If some codes have a 2-letter prefix and others 3, a fixed position cuts the wrong characters. Use FIND or SUBSTITUTE instead.
4. Forgetting the result is text. =SUBSTITUTE("1,250",",","") returns the text "1250". Wrap it in VALUE, as in =VALUE(SUBSTITUTE(A2,",","")), if you need to do maths with it.
5. Not using quotes. Text arguments need double quotes: ".", not ..
6. Overwriting the source. Build the result in a helper column, check it, then paste it as values.

Spaces and invisible characters are a separate problem that neither function spots easily. Our comparison of TRIM vs CLEAN covers them, and the TRIM function guide explains how TRIM handles extra spaces. If you're on Microsoft 365 or Excel 2024, TEXTBEFORE and TEXTAFTER can also split text at a delimiter; see which functions each Excel version includes.

Our recommendation#

Reach for SUBSTITUTE first. Most cleaning jobs are about specific characters or words, and SUBSTITUTE handles them without counting positions. Use REPLACE when the position is what matters: fixed-format codes, masking, inserting a character at a known place, or removing a variable-length start with FIND.

In short: SUBSTITUTE when you know what, REPLACE when you know where.

Practise it#

Try the REPLACE exercise on this page, then open the cleaning messy text lesson to tidy a CRM export in a live sheet: TRIM the names, fix capitals with PROPER, UPPER and LOWER, and nest two SUBSTITUTE formulas to turn mixed phone separators into dashes.