Substitute function in excel is a very useful function which is used to replace or substitute a given text with another text in a given cell, this function is widely used when we send massive emails or messages in a bulk, instead of creating separate text for every user we use substitute function to replace the information.
Excel Substitute Function (Table of Contents)
What is a Substitute Function in Excel?
Substitute function is text function which is used to replace one or more strings with another text string. Substitute function can be useful for when you want to substitute old text with a new text in the given text. For example, if the given text is 99-11-27-11-28 and you want it as 9911271128 then you can use this function to transform this.
The SUBSTITUTE function is as similar to the REPLACE function used it the excel. However, SUBSTITUTE function replaces one or more instances of a substituted text string. Whereas REPLACE function only replaces the text in a specified position/location of a supplied string. Basically, it is used to replace the text strings from imported data.
Substitute Formula in Excel
Formula and Explanation
Substitute function in excel has four parameters three (text, old_text,new_text) are compulsory parameters and one (instance_num) is optional.
Compulsory Parameter:
- text: It is a text from which we want to substitute some text.
- old_text: It is text which is going to replace.
- new_text: It is the text which replaces the old text.
Optional Parameter:
- [instance_num]: it specifies the occurrence of old_text. if you specify the instance only that instance will have replaced by substitute function otherwise all instances replaced by it.
How to Use Substitute Function in Excel?
Substitute function is very simple and easy to use. Let understand the working of Substitute function by some examples.
Example #1
In the first example, we have will substitute the “_” with space in the given set of Name data.
To get the desired output Tanuj Rajput from Tanuj_Rajput apply the Substitute formula SUBSTITUTE(A2,”_”,” “,1)
It will replace the first instance of “_” with space and you will get the desired data as output.
Example #2
In this example, we will replace the first instance of character “a” with “w” in the given dataset of full name by using the substitute function.
Let’s apply the =SUBSTITUTE(A2,”a”,”w”,1) formula in the Formula column,
As shown in the below table and you will get the output in the Output column, shown in the third column.
Example #3
In this example, we will replace all instances of character “a” with “w” in the given data set
By using the substitute formula excel =SUBSTITUTE(B38,”a”,”w”)
And drag it to in the formula column to the output with no “a” values as shown in the below table.
Example #4
In this example, we will replace all the spaces with blank from the given set of Full names.
Here, we will apply the below substitute formula to achieve this =SUBSTITUTE(I8,” “,””)
By using this you will get the Output without space as shown in the below table.
Things to Remember about the Substitute Function in Excel
- Substitute function is the case-sensitive function.
- Substitute function considers tanuj and Tanuj as different values, means it can distinguish b/w lower case and upper case.
- Substitute function does not support wildcard characters i.e. “?” ,“*” and “~” tilde.
Recommended Articles
This has been a guide to Substitute in Excel. Here we discuss how to use Substitute function in excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- VBA Transpose
- VBA Sub
- Remove Space in Excel
- DSUM Function
- Minimum in Excel
- TRANSPOSE Function Excel
- Excel Subtraction Formula
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
