SUBSTITUTE function is used when you want to substitute text with new specified text in a string in both excel and Google Sheet.
=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.
In the below example we have used the SUBSTITUTE function to substitute “ab” with “v”. 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 last argument. That’s why it has replaced all occurrences of “ab” with “v”.
SUBSTITUTE works for special characters as well. In below example we have substituted “**” with “&&” without mentioning the instance number. It considered substituting from the first occurrence of “**” till the end of the string. That’s why only a single “*” remains.
- SUBSTITUTE is case sensitive.
- The instance number that is the last argument cannot be negative or 0.