How to Use the SUBSTITUTE Function in Excel (7 Examples)

In this article, we will demonstrate some uses of the SUBSTITUTE function with examples.

Excel SUBSTITUTE Function: Quick View

Introduction to the SUBSTITUTE Function in Excel

The SUBSTITUTE function is categorized under the Text functions category in Excel’s function library. It replaces matching text in a given string.

Summary:

Replaces existing text with new text in a text string.

Syntax:

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

Argument Explanation:

Versions:

Available from Excel 2007 onwards.

How to Use the SUBSTITUTE Function in Excel: 7 Examples

Now we’ll present some basic examples of the SUBSTITUTE function. We’ll also demonstrate the difference between the SUBSTITUTE and REPLACE functions.

Example 1 – Making Changes to the Original String

Consider the following dataset consisting of a few random text strings.

Let’s change some of the text using the SUBSTITUTE function.

• Enter the following formula in cell D5 and press ENTER..

Here, B5 is the text, our target word is “Sales”, and the word will be changed to “Cost”.

After applying the same process to the rest of column D, the result is as follows:

Example 2 – Changing a Particular Instance

Suppose the text we want to replace occurs multiple times within the text. We can change a particular instance by providing the instance number as the last argument in the SUBSTITUTE function.

In our sample dataset, every row contains redundant letters. Suppose we want to replace the lower case starting s in superstar in cell B5 with an upper case S.

• Enter the following formula in cell C5:

This function changes all the “s” characters to “S”.

But we only wanted the first lowercase “s” changed, not all three, so we need to specify the instance number. Here, we want to change the string’s second “s“, so we enter 2 for the instance_num argument.

• Enter the following modified formula in cell C5:

Now, only the correct instance of “s” has been replaced by “S”.

Example 3 – Changing a Special Character

Suppose we need to replace a special character with another character. In the dataset below, let’s replace the (–) sign with the (*) sign using the SUBSTITUTE function.

• In cell C5 insert the following formula:

The SUBSTITUTE(B5,”-“,”*”) syntax replaces all the (–) signs with the (*) sign.

The outcome is as in the image below.

Method 4 – Nested SUBSTITUTE Functions for Multiple Changes

A single SUBSTITUTE function will change one text at a time. To change multiple strings at once, we’ll need to use multiple SUBSTITUTE functions together.

Consider the following dataset of a few footballers with their abbreviated names and numbers.

Let’s find the full name from the short name. The short name consists of the first and last name initials. We’ll replace the initials with the unabbreviated names using nested SUBSTITUTE functions.

• In cell C5 enter the following formula:

The inner SUBSTITUTE function operates first, changing CR7 to Cristiano R7. The output is passed to the outer SUBSTITUTE function, which replaces R with Ronaldo to produce the final output.

Example 5 – Word Count Using the SUBSTITUTE Function

We can count the number of words present in a string using the SUBSTITUTE function, in conjunction with the LEN and TRIM functions.

• In cell C5 enter the following formula:

• Press ENTER and drag the formula down to the cells below using the Fill Handle.

To find the total number of words in the range, we can use the SUMPRODUCT function with the previous formula.

• The formula is follows:

The function works like previously, but this time we have provided a range, so the inside portion of the SUMPRODUCT function returns an array of word counts for every row.

The total word count is returned, as shown in the above image.

Example 6 – Retrieve a Specified String Based on the Delimiter

Consider the following dataset of Products with their Product Ids. Let’s retrieve the last part of the Product Id based on the delimiter (“-“).

• In cell D5 enter the following formula:

• Press ENTER and drag the same formula down to the rest of the cells in the column.

The result is as in the image below.

Example 7 – Using the SUBSTITUTE Function for Multiple Characters

We can change multiple strings using the SUBSTITUTE and INDEX functions together. The INDEX function finds the value to change, and the SUBSTITUTE function then changes it.

• In cell C5 enter the following formula:

Here, $B$13:$B$14 is the text we want to change, and $C$13:$C$14 is the text we want after changing.

• Press ENTER and drag down the Fill Handle to return the output below.

SUBSTITUTE vs REPLACE Function in Excel: What’s the Difference?

• The SUBSTITUTE function is a simpler but more comprehensive alternative to the REPLACE function. The REPLACE function is only suitable when the numerical start_num and num_chars arguments are known. The SUBSTITUTE function on the other hand, doesn’t require the start_num or num_char arguments. It only requires the cell name, the word or the text to be substituted, and the substitute word.

Common Errors While Using the SUBSTITUTE Function

Download Practice Workbook

<< Go Back to Excel Functions | Learn Excel