Substitute Function in Excel
Substitute function in Excel replaces any text or number into another text or number.
In simple words, if you want to remove some text or number from the cell, you can remove it or you can easily replace it.
Syntax
=SUBSTITUTE (text, old_text, new_text, [instance])
Parameters Exaplained:
- text - The text you want to change
- old_text - In the selected text which text to replace.
- new_text - The new text you want to replace with.
- instance - Optional, the instance to replace.
Substitute Function Examples
In the below example, you can see a name with spaces in between two texts.
you can also remove space using a TRIM function in Excel. but this function will not remove all spaces in between the texts. but using a substitute excel formula we can remove or replace those spaces.
To replace spaces in between the texts follow the below steps.
1. First, use the Substitute function in cell B4 and select the arguments. (which we discussed in the syntax part).
2. After applying arguments with the formula you will get the below result.
Result is
Here the applied formula is=SUBSTITUTE(B2," ",""). which means this formula is replaced " " (spaces) into "" (blank).
3. To apply percentage format go to Home Tab, Number Group then click on percentage symbol.
Substitute Example
Let us take one more example.
Here you can see a name with a hyphen (- -) in between two texts.
Now we need to replace hyphen (- -) into ** using substitute function.
Formula applied here is =SUBSTITUTE(F2,"-"," * "). which means this formula is replaced " - - " (hyphens) into " * * ".
This is a simple way to use the SUBSTITUTE function in Excel.
