Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print
Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print

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:

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 »
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 »