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