SUBSTITUTE and REPLACE in Excel

The Excel SUBSTITUTE function swaps one piece of text for another based on what it says, and REPLACE swaps characters based on where they sit. Use SUBSTITUTE to change every dot in a phone number to a dash; use REPLACE to overwrite the first three characters of a code. This guide is for anyone cleaning imported data or preparing for an Excel test, and it shows both functions on small, realistic examples.

SUBSTITUTE vs REPLACE: which one do you need?#

Pick SUBSTITUTE when you can name the text to change, and REPLACE when you can only say where it is.

Both functions return text and leave the original cell unchanged, so you build the clean version in a new column. Both work in every version of Excel, including Excel for Mac and Excel for the web. For a deeper side-by-side, see SUBSTITUTE vs REPLACE.

How to use the Excel SUBSTITUTE function#

SUBSTITUTE takes the text, the part to remove, the part to put in, and an optional occurrence number.

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

Here is a list of phone numbers exported from a CRM:

In B2, type:

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

Excel finds every dot in A2 and puts a dash in its place, giving 555-201-7788. Fill down with Ctrl+D (Windows) or ⌘D (Mac) to clean the rest of the column.

How to change only one occurrence#

Add the fourth argument, instance_num, to change only the first, second or third match. Take the code a-b-c-d:

=SUBSTITUTE("a-b-c-d","-","/",2)

The result is a-b/c-d. Only the second dash changed. This is handy when a code uses the same separator for two jobs, such as a date inside a product reference.

How to remove characters with SUBSTITUTE#

Use an empty string, "", as the new text to delete every copy of a character. To strip dashes from an ID:

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

TX-ELC-40512 becomes TXELC40512. The same trick removes thousand separators from numbers that arrived as text: =VALUE(SUBSTITUTE(A2,",","")) turns the text "12,500" into the number 12500. VALUE matters, because SUBSTITUTE always returns text, and SUM skips text.

Removing a character is also the basis for counting it. The length of the text minus the length without the character tells you how many there were, which our guide to counting characters and words in a cell explains step by step.

How to fix several characters in one formula#

SUBSTITUTE handles one old_text at a time, so you nest one inside another to fix two or more different characters. Some phone numbers use dots and others use spaces:

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

Excel works from the inside out. The inner SUBSTITUTE changes dots to dashes, then the outer one changes spaces to dashes. You can nest as many as you need, but beyond three or four the formula gets hard to read. At that point, Find and Replace or Power Query is often quicker for a one-off clean.

Stray spaces are a different problem. If a cell has extra spaces at the start, at the end or doubled in the middle, wrap it in TRIM first. The TRIM function guide covers what it removes and what it misses, and our article on cleaning messy data with TRIM, CLEAN and PROPER shows the full clean-up routine.

How to use REPLACE to change text by position#

REPLACE overwrites a number of characters starting at a position you choose, whatever those characters are.

=REPLACE(old_text, start_num, num_chars, new_text)

Invoice numbers like INV-2024-0081 need the year updated. The year starts at character 5 and is 4 characters long:

=REPLACE(A2,5,4,"2025")

The result is INV-2025-0081. SUBSTITUTE could also do this, but only if every cell held the same old year. REPLACE doesn't care what the four characters say.

Insert text without removing anything#

Set num_chars to 0 to insert text. =REPLACE("5552017788",4,0,"-") gives 555-2017788: nothing is removed, and the dash is added before character 4.

Mask part of an ID#

REPLACE combines well with REPT, which repeats a character. To hide all but the last four digits of an account reference:

=REPLACE(A2,1,LEN(A2)-4,REPT("*",LEN(A2)-4))

For 5551234567 the result is ******4567. LEN works out how many characters to hide, so the formula fits any length. Our guide to REPT and in-cell bars shows more uses for repeated characters.

How to find the position for REPLACE#

When the position changes from row to row, let FIND or SEARCH work it out. To replace everything before the @ in an email with "user":

=REPLACE(A2,1,FIND("@",A2)-1,"user")

FIND returns the position of the @, and subtracting 1 gives the number of characters before it. The guide to finding the position of text with FIND and SEARCH covers how both work, and LEFT, RIGHT and MID shows how to pull text out instead of swapping it.

Common mistakes with SUBSTITUTE and REPLACE#

These are the errors that come up most often:

• Expecting SUBSTITUTE to ignore capitals. It is case-sensitive. =SUBSTITUTE(A2,"ltd","Limited") leaves "Ltd" alone. Use =SUBSTITUTE(LOWER(A2),"ltd","limited") if the capitals vary and lowercase output is fine.
• Swapping the argument order. SUBSTITUTE wants the old text before the new text. =SUBSTITUTE(A2,"-",".") does the opposite of what you meant.
• Forgetting the quotes. Text arguments go in double quotes: ".", not ..
• Adding numbers that are now text. Wrap the result in VALUE before you sum it.
• Giving REPLACE a position that moves. A fixed start_num breaks when codes have different lengths. Use FIND to calculate it.
• Using SUBSTITUTE to split text. If you want the pieces in separate cells, TEXTSPLIT splits text into columns in one formula.

Practise it#

The fastest way to remember the argument order is to type it a few times on real data. Try the live exercise on this page, then work through the cleaning messy text lesson, where you fix phone separators with nested SUBSTITUTE and tidy names with TRIM and PROPER in a live spreadsheet.