SUBSTITUTE Function Excel
In this Article
This Excel Tutorial demonstrates how to use the Excel SUBSTITUTE Function in Excel to find and replace text, with formula examples.
SUBSTITUTE Function Description:
The SUBSTITUTE Function Finds and replaces existing text with a new text string. Case-sensitive.
|Replace Text||=SUBSTITUTE(C5,D5,E5)||A String A Text|
|Replace Only First Instance||=SUBSTITUTE(C6,D6,E6,F6)||A String The Text|
|Replace Only 2nd Instance||=SUBSTITUTE(C7,D7,E7,F7)||The String A Text|
|String Not Found.||=SUBSTITUTE(C8,D8,E8)||The String The Text|
|Case Doesn’t Match||=SUBSTITUTE(C9,D9,E9)||The String The Text|
Syntax and Arguments:
The Syntax for the SUBSTITUTE Formula is:
Function Arguments ( Inputs ):
text – The original string of text.
old_text – The text string that you wish to find and replace.
new_test – The replacement text.
instance_num – OPTiONAL. The instance number to replace. If blank, all instances are replaced.
The SUBSTITUTE Function is case sensitive!. You can use the LOWER or UPPER Functions to convert your strings of text into a consistant case before using SUBSTITUTE Function. Then, if desired, you can use the PROPER Function to capitalize the first letter of each word.
Alternatively, you can use the Functions SEARCH and REPLACE to simulate a non case-sensitive SUBSTITUTE. Use SEARCH Function to find the starting position of the text string. The SEARCH Function is not case-sensitive. Then use REPLACE Function to replace the text. You will need to use the result from the SEARCH Function as the start_num input in REPLACE. You will also need to define the number of characters to replace ( num_chars). You can manually count them or use the LEN Function to count the number of characters. Example:
Substitute – Remove Hyphens
A common question is how to remove hyphens from text. The trick is to Substitute the hyphen with nothing.
For this example, let’s assume cell A1 has text with hyphens in it. Here’s how to remove them
1. In a blank helper cell type =SUBSTITUTE(
2. Click on the cell you would like to substitute characters in or A1 (this automatically fills your formula)
3. Type, (comma)
4. Type “-”
5. Type, (comma)
6. Type “” and hit enter
Your end result in your helper cell should look like this:
3 More Substitute Examples:
Looking at cell B3, we want to replace the word “sick” with “large”. We can use the following expression:
If the text that we want to replace occurs more than once then we need to specify the instance that we mean. In the second example, we have two occurences of the word “old”. So if we want to change second instance to the word “grey” then we have to use:
Note that if the occurrence is not specified then all instances are replaced. So:
Would get rid of all instances of the word “old” and replace them with the word “grey”. Note that if the old text cannot be found then the string is unchanged. So looking at the last example:
Means that we try and replace the word “black” with the word “grey”. However, as “black” does not occur the original string remained levitra shipped in the united states unchanged:
SUBSTITUTE Examples in VBA
You can also use the SUBSTITUTE function in VBA. Type:
For the function arguments (text, etc.), you can either enter them directly into the function, or define variables to use instead.
When the following code is executed
Range("A1") = WorksheetFunction.Substitute(Range("A1"), "using", "", 1)
The contents of cell A1 will become:
So, the word “using” was replaced by an empty string. The last parameter (1) indicates which instance of the text should be replaced, in case the text is present more than one times. So, for example if we execute the following statement
Range("A1") = WorksheetFunction.Substitute(Range("A1"), "the", "an", 1)
The A1 cell will look like this
Notice that the first occurrence of “the” was replaced by “an”, while the second “the” was not affected. If we omitted the last parameter, both “the” words would have been replaced
Return to the List of all Functions in Excel
How to use the SUBSTITUTE Function in Excel:
To use the AND Excel Worksheet Function, type the following into a cell:
After entering it in the cell, notice how the AND formula inputs appear below the cell:
You will need to enter these inputs into the function. The function inputs are covered in more detail in the next section. However, if you ever need more help with the function, after typing “=SUBSTITUTE(” into a cell, without leaving the cell, use the shortcut CTRL + A (A for Arguments) to open the “Insert Function Dialog Box” for detailed instructions:
For more information about the SUBSTITUTE Formula visit the