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