Excel SUBSTITUTE() Function

The SUBSTITUTE() function in excel substitutes/replaces the old text with the new text in a string. SUBSTITUTE function is used to clean data. This function is case-sensitive.

Syntax

SUBSTITUTE(Text, Old_Text, New_Text, [instance_number])

Parameter

Text (required)- This parameter represents the text or string in which you want to modify or replace it.

Old_Text (required)- This parameter represents the old text which you want to replace.

New_Text (required)- This parameter represents the new text with which you want to modify your original text.

instance_number (optional)- This parameter represents the instance of old text which you want to replace with new text. It is optional, but if not provided, it will make changes to all the occurrences of old text with new text.

Category

Text

Return

This function returns a string after replacing the old text with the new text in ‘Text’ parameter.

Example 1

Objective: Replace "t" with "b" for the String column by using the SUBSTITUTE function.

String	Result	Formula
Home and Nuts	Home and Nubs	=SUBSTITUTE(A26,"t","b")
Dim Dumble	Dim DumTle	=SUBSTITUTE(A27,"b","T",1)
the family man	family man	=SUBSTITUTE(A28,"the"," ")
Excel is an easy language	Excel is an easy language	=SUBSTITUTE(A29,"the","a",2)
The car is in the driveway	car is in the driveway	=SUBSTITUTE(A30,"The"," ")

Example 2

Objective: Remove all spaces from 1st Random Value by using the SUBSTITUTE function.

String	Result	Formula
Hulk     Wingson	HulkWingson	=SUBSTITUTE(E106," ","")
World    is        Beautiful!	WorldisBeautiful!	=SUBSTITUTE(E106," ","")