March 26, 2021

SUBSTITUTE function in Excel

In this lesson we will learn how the SUBSTITUTE function works in Excel. SUBSTITUTE function is used when you want to substitute text with new specified text in a string.

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

Input Arguments :

  • text – The string you want to change.
  • old_text – The section of the string that you want to substitute.
  • new_text – The new text string with which you want to substitute old_text.
  • [instance_num] – This denotes which instance of the old_text you need to substitute (in case of multiple occurrences). This is optional, in case this argument is not specified, all the instances of old_text are substituted.

Let’s understand with a simple example. In the below table in cell B1 we have added the SUBSTITUTE function as follows.

=SUBSTITUTE(A1,"ab","v",2)

Here the SUBSTITUE function is used to substitute the text “ab” with “v” in the string in cell A1 . Please note the last argument is 2 hence it replaced only the 2nd occurrence of “ab” with “v”.

In below example we have used the same SUBSTITUTE function but didn’t mention anything in  instance_num.

=SUBSTITUTE(A1,"ab","v")

That’s why it has replaced all occurrences of “ab” with “v” in the string in cell A1 .

SUBSTITUTE works for special characters as well. In the below table in cell B1 we have added the SUBSTITUTE function as follows.

=SUBSTITUTE(A1,"**","&&")

Here we have substituted the characters “**” with “&&” present in the string in cell A1. Here also we didn’t provide any instance_num. It considered substituting from the first occurrence of “**” till the end of the string. That’s why only a single “*” remains.

Note : 
  • SUBSTITUTE is case sensitive.
  • The instance number that is the last argument cannot be negative or 0.

You can check here to understand how the SUBSTITUTE function works in Google Sheet.

Leave a Reply

Your email address will not be published. Required fields are marked *