What is SUBSTITUTE Function in Excel ?
SUBSTITUTE is a function in Excel to replace existing text with a new text in string. It will change all old text with new text unless we give a particular cell reference.
“SUBSTITUTE function replace existing text with new text in a string” SUBSTITUTE
= SUBSTITUTE(text, old_text, new_text, [instance_num])
text – text we want to change (in string)
old_text – the old text to be replaced (in string)
new_text – the new text which we want to replace in place of old text.
[instance_num] – (optional) if any text or word occurrence is multiple time in string then we have to specified number and if after multiple occurrence this argument is not specified, all the instances of old_text will be replaced with new_text.
Useful Notes of SUBSTITUTE function
- When applying SUBSTITUTE function if we specify an instance number then only that number text or word will be replace otherwise on multiple occurrence all the old_text will be replaced with a new_text.
- Instance is optional, if not supplied, all instances of old_text will be replaced with new_text
- SUBSTITUTE function is case-sensitive and does not support wildcards.
How Excel SUBSTITUTE function works? – Example
Below example is showing SUBSTITUTE function, “How SUBSTITUTE function works?”
In the above image we can see that how Excel SUBSTITUTE function works with four example, each one is a different example and we can understand it very easily.
I have explained SUBSTITUTE function in easy way, if there is any doubt or question, please feel free to share with me.