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," ","")



ADVERTISEMENT
ADVERTISEMENT