Home > Formulas > Text > SUBSTITUTE or REPLACE

SUBSTITUTE and REPLACE Functions

Use Microsoft Excel SUBSTITUTE and REPLACE functions to change the contents of a text string, by removing old characters, and adding new characters. See examples, to help you decide which function will work best for your worksheet.

SUBSTITUTE vs REPLACE

If you need a formula to replace old text with new text, which Excel function should you use - SUBSTITUTE or REPLACE?

Here are a few examples of when to use each function, and there are more examples further down the page.

SUBSTITUTE Function

Use the SUBSTITUTE function if you need to make one of the following changes:

• Replace a specific string of text in the existing text
  • Change "East" to "North"
• Replace all instances of the old text
  • In a cell, change all space characters to underscores
• Make a case-sensitive change to the text
  • Change "May" to "June" (do not change "may")

REPLACE Function

Use the REPLACE function if you need to make one of the following changes:

• Replace specific number of characters, at specific starting position
  • Change first 3 numbers to new area code
• Insert characters at specific position
  • Insert colon after 4th character, without removing any characters

Note

In some cases, it might be quicker and easier to use the Find and Replace commands on the Excel Ribbon, instead of creating formulas.

SUBSTITUTE Function

The SUBSTITUTE function is in the Text category of Excel functions, and it replaces old text with new text, in a text string.

This short video shows three examples, and there are written instructions below the video.

SUBSTITUTE Syntax

The SUBSTITUTE function has the following arguments in its syntax:

• SUBSTITUTE(text, old_text, new_text, instance_num)

Here are the details for each of the four arguments:

• text - text string or cell reference, where text will be replaced
• old_text - text that will be removed
• new_text - replacement text that will be added
• instance_num - specific occurrence of old text to replace

SUBSTITUTE Tips

• SUBSTITUTE function can replace ALL instances of the old text
• To replace only one occurrence, use the instance_num argument
• Replacements are case sensitive
  • For non-case sensitive replacements, you can use the REPLACE function

How Could You Use SUBSTITUTE?

Here are 3 examples of how you could use SUBSTITUTE, and the examples are shown in the sections below:

-- Ex 01 -- Change region name in report title

-- Ex 02 -- Remove non-printing characters

-- Ex 03 -- Replace last space character

Ex 1: Change Report Title

In the following example, you can quickly customize a regional report title, by selecting a region name from a drop down list.

Here's where all of the information is located on the worksheet shown in the screen shot below:

• SUBSTITUTE formula is in cell C3
• Report title is entered in cell C11, which is named RptTitle
• Report title has "yyy" as a placeholder for the region name
• Region name is selected in cell D13

Here is the formula in cell C3, which removes the “yyy” in the title text, and replaces that string with the region name selected in cell D13.

Note: The text to replace is inside double quotes in the formula, "yyy", so Excel recognizes it as a text string.

• =SUBSTITUTE(RptTitle, "yyy", D13)

TIP: To calculate the Revenue and Costs, use SUMIFS formulas that refer to the selected region name

Ex 2: Remove Non-Printing Characters

When you copy data from a website, and paste it into Excel, there might be hidden, non-printing space characters in the text

• Normal space characters are character 32 in ASCII text
• Non-breaking space characters are character 160 in ASCII text

If you're cleaning up the imported text, the Excel TRIM function can remove normal space characters (32), but it ignores the non-printing space characters (160).

Instead, you can use the SUBSTITUTE function to replace each of the non-printing spaces with a normal space character. Then, use TRIM to remove all the extra normal space characters

SUBSTITUTE Formula

Here is the formula in cell E3, and copied down to cell E4:

• =TRIM(SUBSTITUTE(B3,CHAR(160),” “))

Ex 3: Replace Last Space Character

Instead of replacing all instances of a text string, you can use the SUBSTITUTE function’s instance_number argument to select a specific instance.

For example, to replace the first occurrence of a space character in cell B3, with a hyphen, you could use this formula, with the 4th argument set as 1:

• =SUBSTITUTE(B3, " ", "-", 1)

Replace Last Occurrence

In this list of recipe ingredients, we want to replace the last space character only

In cell C3, the LEN function calculates the number of characters in cell B3. The SUBSTITUTE function replaces all the space characters with empty strings, and the second LEN function finds the length of the revised string. The length is 2 characters shorter, so there are two spaces.

SUBSTITUTE Formula with Helper

First, here is the formula in cell C3, to calculated the number of space characters in cell B3:

• =LEN(B3)-LEN(SUBSTITUTE(B3," ",""))

Next, in cell D3, the SUBSTITUTE formula replaces only the 2nd space character with the new text - space, pipe character, space -- " | "

• =SUBSTITUTE(B3, " ", " | ", C3)

Note: Instead of using a "helper" column for this formula, you could combine the two formulas into one longer formula.

• =SUBSTITUTE(B3, " ", " | ", LEN(B3)-LEN(SUBSTITUTE(B3, " ", "")))

REPLACE Function

The REPLACE function is in the Text category of Excel functions, and it replaces a specified number of characters in a text string, with new text.

This short video shows three examples, and there are written instructions below the video.

REPLACE Syntax

The REPLACE function has the following arguments in its syntax:

• REPLACE(old_text, start_num, num_chars, new_text)

Here are the details for each of the four arguments:

• old_text - text string or cell reference, where text will be replaced
• start_num - position where old characters start
• num_chars - number of old characters to replace
• new_text - text that will replace the removed text

REPLACE Tips

• REPLACE function replaces a specified number of characters at the indicated starting position.
• To replace a specific text string, anywhere in the original text, use the SUBSTITUTE function instead

How Could You Use REPLACE?

Here are 3 examples of how you could use REPLACE, and the examples are shown in the sections below:

-- Ex 01 -- Change area code in phone number

-- Ex 02 -- Replace first space with colon and space

-- Ex 03 -- Use nested REPLACE to insert hyphens

Ex 1: Change Phone Area Code

In this example, you can change the first three digits in a list of phone numbers, where a new area code was introduced.

Here's where all of the information is located on the worksheet shown in the screen shot below:

• REPLACE formula is in cell D3
• New area code is in cell C3
• Old phone number is in cell B3

Here is the formula in cell D3, which removes 3 characters, from the old phone number, starting from character 1, and adds the new area code, from cell C3

• =REPLACE(B3, 1, 3, C3)

Ex 2: Replace First Space Character

In the example shown below, we want the formula to replace the first space character with a colon and space character.

In each cell, the first space could be in either position 5 or position 6, so we can't "hard code" that number into the formula.

Instead, you can use the FIND function, to locate the first space character.

REPLACE Formula

Here is the formula in cell C3, and copied down to cell C5:

• =REPLACE(B3, FIND(" ", B3, 1), 1, ": ")

The second argument, start_num, is the result of the FIND formula.

Ex 3: Insert Multiple Hyphens

The REPLACE function can be nested, to make multiple replacements in the old text string.

In this example, each phone number needs to have two hyphens inserted:

1. after the first 3 numbers
2. after the second 3 numbers

NOTE: None of the existing characters in the phone number should be removed

Nested REPLACE Formula

First, here is the nested REPLACE formula in cell C3, and copied down to cell C4:

• =REPLACE(REPLACE(B3, 4, 0, "-"), 8, 0, "-")

The third argument, num_chars, is set a zero, so none of the existing characters in the phone number will be removed.

Video: Add or Remove Line Breaks

In this short video, Sarah shows how to add a manual line break in a cell. Then, see how to find and replace line breaks in a group of cells on the worksheet. Written steps are on the Add or Remove Line Breaks page.

Video Timeline

• 00:00 Introduction
• 00:06 Create a Line Break
• 00:17 Wrap Text
• 00:23 Remove Line Breaks
• 00:33 Find and Replace
• 00:44 Find What
• 01:01 Replace With
• 01:14 Find Next
• 01:27 Line Break Shortcuts

Note: The full video transcript is available on the Add or Remove Line Breaks page.

Video: Find and Replace With Wildcards

In this video, you'll see how to use the * wildcard with the Excel Find and Replace commands, to clean up the names in a contact list. There are written steps, and the full video transcript on the Excel Find and Replace commands page.

Video Timeline

• 00:00 List of Names to Clean Up
• 01:02 Make a Backup of Data
• 01:30 Remove Colons and Text
• 02:30 Remove Hyphens and Text
• 03:00 Remove OF and Text

Get the Practice Files

• SUBSTITUTE vs REPLACE: To see the examples from this page, download the SUBSTITUTE vs REPLACE workbook. The zipped file is in xlsx format, and does not contain any macros.
• Check Item Count with SUBSTITUTE: Use LEN, TRIM and SUBSTITUTE functions to calculate how many items are in a cell, separated by commas or space characters. See instructions on Contextures Excel Blog. The zipped file is in xlsx format, and does not contain any macros.

More Functions

Find and Replace Command

FIND Function

SEARCH function

TRIM Function

TEXTJOIN Function