Rational
Find a text string and substitute with a second text string.
Description
The Excel SUBSTITUTE function replace the specific text in a text string that occurs in a specific location. The function replaces by selecting one or more instances of the occurrence of a text string.
For example, =SUBSTITUTE(“Butter”,”t”,”l”,2) return “Butler”, substitute 2nd instance “t” occurrence.
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])
Parameters
text – The text for which to substitute characters
old_text – The text to remove
new_text – The text to replace
instance [optional] – Specify the instance of the occurrence of old_text to be replaced. Otherwise if not supplied, every occurrence of old_text in the text is changed to new_text.
Return Value
Modified Text.
Key Notes
The SUBSTITUTE function is case-sensitive. We can use the REPLACE function to replace certain set of characters with worrying about case-sensitiveness.
If instance parameter does not opt for any value (not even blank or zero), every occurrence of old_text in the text is changed to new_text.
Blank or “Zero” argument of instance argument, will return #VALUE! error.
From SUBSTITUTE Function you can change Number suppose that you have value “453” but you notice it was not correct to replace with correct number you will use =SUBSTITUTE(“453”,5,6) it return “463”.
Keep Reading Similar Functions:
FIND
The FIND function displays the numerical position of the character in a string. I.e. to find the 2nd occurrence of 8, =FIND(8,128128,4) returns 6.
SEARCH
The SEARCH function finds the position of a specified character or sub-string within a text string. I.e., =SEARCH(“ring”,”String”,1) return “3”, “ring” begins at the third character of “String”.
REPLACE
The REPLACE function substitute part of a text string with a new text string. I.e., =REPLACE(“Michael Jackson”,9,6,”Jorda”) returns “Michael Jordan”.