Excel SUBSTITUTE Function – How to Use

The SUBSTITUTE function in Microsoft Excel is a handy tool that enables users to replace single or multiple instances of a specific character or text string with a different character or text string. To see how this works on a spreadsheet, we will discuss the syntax and four arguments of the Excel SUBSTITUTE function.

Next, we will try our hand at some SUBSTITUTE formulas by working through a couple of examples. SUBSTITUTE is an in-built worksheet function categorized as a String/Text function in Excel. The SUBSTITUTE function is case-sensitive and does not support wildcard characters.

Excel SUBSTITUTE Function

Syntax

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

Arguments:

text – This is a required argument where you will input the original text string within which you wish to substitute the character(s) supplied through the old_text argument. To supply a value for this argument, you may use a cell reference, enter a text string, or use a return supplied by another formula.

old_text – This is a required argument where you will input the character(s) or text string you wish to substitute.

new_text – This is a required argument where you will input the character(s) or text string you wish to substitute the contents of old­_text argument with.

nth_instance – This is an optional argument that lets you instruct Excel as to which instance of the character(s) or text string in the old_text argument you will like to replace. If omitted, the function will substitute all instances of the character(s) or text string entered in the old_text argument.

Important Characteristics of the SUBSTITUTE function

  • SUBSTITUTE function is a helpful tool that enables you to replace specific character(s) or text string by entering them in the old_text argument, with character(s) or text string entered in the new_text
  • The optional nth_instance argument enables you to choose a specific instance of the character(s) or text string entered in the old_text The omission of this argument will result in the replacement of all old_text instances with character(s) or text string supplied in the new_text argument.
  • SUBSTITUTE is a case-sensitive function and does not support wildcard characters.

Examples of the Excel SUBSTITUTE function

Here are some basic examples of how you can incorporate and use the SUBSTITUTE function in your worksheet. To get a firm hold on the workings of these formulas, pull up a spreadsheet and try using these formulas alongside, as you read through the examples.

1. Substitute all Instances of a String or a Character

Excel-SUBSTITUTE-function-Example-01

=SUBSTITUTE(A2,"1","3")

Let’s break down the formula’s instructions to Excel and see what it looks like using words. In effect, we are asking Excel to: Look at the contents in cell A2 (text argument) and locate all the "1"s (old_text argument) in that cell. Next, substitute all instances of "1" (since we did not supply the nth_instance argument) with "3" (new_text argument).

Here, Excel will substitute all instances of "1" and return "Floor 3, Room 3".

2. Substitute a Specific Instance of a String or a Character

Excel-SUBSTITUTE-function-Example-02

=SUBSTITUTE(A2,"1","3", 2)

This is the same formula we used in the previous example, but with the nth_instance argument supplied as 2. The nth_instance­ argument will restrict the substitution to only the specified instance, which in our example is the 2nd instance of "1". Therefore, the function will only substitute the 2nd instance and return "Floor 1, Room 3".

3. Case-sensitivity of the SUBSTITUTE function

Excel-SUBSTITUTE-function-Example

=SUBSTITUTE(A2,"Room","Chamber")

Vs.

=SUBSTITUTE(A2,"room","Chamber")

Notice how the formula substitutes text string only when you enter the appropriate case. Using "room" instead of "Room" results in no substitution at all.

4. Nested SUBSTITUTE formula

Excel-SUBSTITUTE-function-Example-05

=SUBSTITUTE(SUBSTITUTE(A2,"Floor","Floor No."), "Room", "Room No.")

A nested SUBSTITUTE formula allows us to replace multiple text strings using a single formula. For example, let’s assume we want to add a text string "No." after both "Floor" and "Room". We would need to use the following formulas:

=SUBSTITUTE(A2,"Floor","Floor No.")

And

=SUBSTITUTE(A2,"Room","Room No.")

Instead, we merge these formulas and nest one SUBSTITUTE formula into another like shown above. The nested formula will involve 2 steps.

  • Step-1: It will first work on the "nested" formula, i.e. the one inside the brackets of the first SUBSTITUTE formula. So, the formula will first compute the return for substitution of the text string "Floor" with "Floor No." At this point, this is what the return looks like: "Floor No.1, Room 1"
  • Step-2: Next, the second SUBSTITUTE formula, i.e. the one outside the brackets, looks for the text string "Room" in "Floor No.1, Room 1" (which is the return from step 1) and substitutes "Room" with "Room No." to give us our desired return:"Floor No.1, Room No.1″

Excel REPLACE Function

Excel REPLACE function is very similar to the SUBSTITUTE function, it can be used for replacing a sequence of characters in a string/text with another set of characters. Let’s briefly go over the syntax of this function.

Syntax

=REPLACE(old_text, start, number_of_chars, new_text)

Arguments:

old_text – The original string value works the same way as in the SUBSTITUTE function.
start – The second argument is the position of the character(s) you want to replace.
number_of_chars – The third argument is where you input the number of character(s) you want to replace, starting from the position mentioned in the second argument.
new_text – The fourth argument is similar to the SUBSTITUTE function’s new_text argument. It is the text string you want to replace the specified character(s) with.

Example of Excel Replace Function:

The only difference between the SUBSTITUTE and REPLACE function is the method of identifying the strings that need to be substituted or replaced. Let’s work through an example of the REPLACE function to see how it uses a different method to accomplish a purpose similar to that of the SUBSTITUTE function.

Excel-SUBSTITUTE-function-Example-06

=REPLACE(A2,"7","1","2")

As we can see, the formula returns "Floor 2, Room 1" since we replaced the "1" with "2". Notice how the REPLACE function does not allow specifying which instance of a particular character or text string we want to replace. It makes intuitive sense because we are already entering the position of the character rather than matching a specified character(s) or text string like with the SUBSTITUTE function.

SUBSTITUTE VS REPLACE – When to choose Which?

If you do not know the specific character(s) of the text string you want to replace, but know the position of those character(s) in the text string, use the REPLACE function. If regardless of the position, you want to change the character(s) in the text string by matching a specified text string, use the SUBSTITUTE function.

I hope these examples gave you a good insight into how you can use the SUBSTITUTE function in your worksheets. Spend a little time with these formulas, and you will champion them in no time. I will see you soon with another powerful Excel function.

About Ankit Kaul

Well, I am Ankit Kaul, the founder of Excel Trick. I am a die-hard fan of Microsoft Excel and have been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'. Check out more about me here.