SUBSTITUTE

Substitutes new text for old text in a text string in Excel
Substitutes new text for old text string

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:

Return character position of a text in Excel

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.

Read More »
Finds position of one text value within another Text in Excel

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

Read More »
To replace part of a text string in Excel

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

Read More »