Substitute Function 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.

• 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.

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

• 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,) 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.

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:

• : 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.

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.

Example #1

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

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

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

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.

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

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

Example #3

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

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

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

Example #4

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

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

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

• 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.

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: –

• VBA Sub
• DSUM Function
• Minimum in Excel
• Excel Subtraction Formula
• How to Create an Excel Spreadsheet?