FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

Substitute Function In Excel

Updated on January 2, 2024
Article byTanuj Kumar
Edited byNannila Jai Ratna
Reviewed byDheeraj Vaidya, CFA, FRM

What Does Substitute Function Do In Excel?

The SUBSTITUTE function in Excel is useful to replace or substitute a given text with another text in a given cell. This function is widely used when we send massive emails or messages in bulk. Instead of creating separate text for every user, we use a SUBSTITUTE function to replace the information.

For example, consider the below example with cell A1 showing a saying. We can see that the saying is typed incorrectly. Instead of ‘Honesty is the best policy’, it is wrongly typed as ‘Honesty is a best policy’.

Now, let us learn how to use the SUBSTITUTE function.

Substitute Function in Excel - 1
  • Step 1: First, select a cell to display the result. In our example, we have selected cell B1.
  • Step 2: Next, insert the SUBSTITUTE formula in cell B1.
  • Step 3: Select the arguments such as =SUBSTITUTE(A1,”a”,”the”)
  • Step 4: Press Enter key.

We can see that the word ‘a’ is substituted with ‘the’ in cell B1.

Substitute Function in Excel - 2

Likewise, we can obtain the results using the SUBSTITUTE function.

Key Takeaways

  • The SUBSTITUTE function in Excel is used to substitute text in the worksheet. It was introduced in Excel in the year 2007.
  • The formula of SUBSTITUTE function is =SUBSTITUTE(text,old_text,new_text,[instance_num]) where text, old text, and new text are mandatory arguments and instance num is the only optional argument.
  • REPLACE and SUBSTITUTE functions are both similar but the SUBSTITUTE function substitutes at multiple positions.
  • The function reads upper case and lower case values as two different values. For example, Excel reads ‘Apple’ and ‘apple’ as two different values.

Syntax

Substitute Formula in Excel

The SUBSTITUTE function in Excel has four parameters, three (text, old_text,new_text) are compulsory parameters and one (instance_num) is optional.

Compulsory Parameter:

  • text: It is a text from which we want to substitute some text.
  • old_text: It is a text which is going to replace.
  • new_text: It is the text which replaces the old text.

Optional Parameter:

  • [instance_num]: it specifies the occurrence of old_text. if you specify the instance only, that instance will have replaced by a substitute function; otherwise, all instances are replaced by it.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How To Use Substitute Function In Excel? (With Steps)

You can download this Substitute Function Excel Template here – Substitute Function Excel Template

We can easily use SUBSTITUTE function in Excel to substitute the values with the below steps:

  • Step 1: First, select a cell to display the result.
  • Step 2: Next, insert the SUBSTITUTE formula in the desired cell.
  • Step 3: Select the values in the arguments.
  • Step 4: Press Enter key.

Likewise, we can obtain the results using the SUBSTITUTE function.

Now, let us have a look at the following examples to understand better.

Examples

Example #1

In the first example, we will substitute the “_” with space in the given set of Name data.

Substitute Function Example 1

To get the desired output, Tanuj Rajput from Tanuj_Rajput apply the Substitute formula SUBSTITUTE(A2,”_,” “1)

Substitute Function Example 1-1

It will replace the first instance of “_” with space, and you will get the desired data as output.

Substitute Function Example 1-2

Example #2

In this example, we will replace the first instance of character “a” with “w” in the given dataset of full names by using the SUBSTITUTE function.

Substitute Function Example 2

Let us apply the =SUBSTITUTE(A2,” a”,” w”,1) formula in the “Formula” column:

Substitute Function Example 2-1

As shown in the below table, you will get the output in the “Output” column, shown in the third column.

Substitute Function Example 2-2

Example #3

In this example, we will replace all instances of character “a” with “w” in the given data set.

Example 3

By using the substitute formula excel =SUBSTITUTE(B38,”a”,”w”)

Example 3-1

And drag it in the “Formula” column to the “Output” with no “a” values, as shown in the below table.

Example 3-2

Example #4

In this example, we will replace all the spaces with blanks from the given set of full names.

Example 4

Here, we will apply the below substitute formula to achieve this =SUBSTITUTE(I8,”, “)

Example 4-1

You will get the output without space, as shown in the table below.

Example 4-2

Important Things To Note

  • The substitute function is the case-sensitive function.
    • The SUBSTITUTE function considers tanuj and Tanuj as different values, which means it can distinguish between lower and upper case.
  • Substitute function does not support wildcard characters, i.e. “?” ,“*” and “~” tilde.

Frequently Asked Questions (FAQs)

1. What is SUBSTITUTE Function in Excel?

The SUBSTITUTE function in Excel is used to substitute misspelled or incorrect text in the worksheet. It is used while sending bulk messages to a large group.

2. How to calculate using SUBSTITUTE function in excel?

Let us learn how to substitute text using SUBSTITUTE function in excel.

For example, consider the below example with a dataset showing the name and places of various people. We can see that ‘Paris’ is wrongly entered as ‘Paros’.

Substitute Function in Excel - FAQ 2

Now, let us learn how to use SUBSTITUTE function in excel.

The steps used to obtain results using SUBSTITUTE function in excel are:

• Step 1: First, select a cell to display the result. In our example, we have selected cell D3.

• Step 2: Next, insert the SUBSTITUTE formula in cell D3.

FAQ 2 - Step 2

• Step 3: Select the arguments such as =SUBSTITUTE(B3,”o”,”i”)

• Step 4: Press Enter key.
We can see that the word ‘Paros’ is substituted with ‘Paris’ in cell D3.

FAQ 2 - Step 4

Likewise, we can obtain the results using the SUBSTITUTE function.

3. What is the difference between SUBSTITUTE and REPLACE functions in Excel?

The SUBSTITUTE and REPLACE functions are both similar but the SUBSTITUTE function substitutes text in one or more places whereas, REPLACE function replaces text only in one position.

This article is a guide to the SUBSTITUTE in Excel. Here, we discuss how to use the SUBSTITUTE function in Excel, along with an Excel example and downloadable Excel templates. You may also look at these useful functions in Excel: –