The Excel REPLACE and SUBSTITUTE functions can be used for replacing a new text string with the existing string in the given text.
See the examples, syntax, and difference between these two functions below.
An example of REPLACE function
The REPLACE function takes four arguments that are required. The syntax of using REPLACE function is:
REPLACE(existing_text, start_number, num_chars, new_text)
I will explain all these arguments, first see the REPLACE function in action. In the example, I will replace word “MS” to “Microsoft” by using Excel REPLACE function:
The REPLACE formula used in the example:
1 =REPLACE("MS Excel is cool",1,2,"Microsoft")
The replaced text is: “Microsoft Excel is cool”
On that examples basis, let me explain the arguments of REPLACE function:
- Existing_text is required. In the example, “MS Excel is cool” is the existing text where you want to use the REPLACE function.
- The second argument value is given as 1 which is the start_number in the existing_text. This is where REPLACE will start replacing the new next. In the context of our example, the start position is first letter i.e. “M”.
- The third argument is num_chars that specify how many characters to replace in the existing text. I used 2 in the REPLACE formula. So replace “MS” by new_string.
- New_text argument specifies the text to be replaced with. In the example, we used “Microsoft”. So the end result is MS is replaced by Microsoft.
The example of REPLACE in cell
Rather than using the hard-coded text, you may use the cell reference for specifying the existing text. In this example, the A3 cell is referred in the REPLACE function. See the formula and example sheet with the result:
1 =REPLACE(A3,7,3,"Visual Basic for Applications")
The example of using FIND function with REPLACE
As mentioned earlier, if you do not know the position of the start_num argument i.e. text starting character that needs to be replaced then you may use FIND and SEARCH functions.
The Excel FIND function returns the position of the given text that can be used as the start_num argument value in REPLACE function. See an example below.
The FIND and REPLACE formula:
1 =REPLACE(A3,FIND("VBA",A3),3,"Visual Basic for Applications")
You can see, we did not provide actual numeric value for the starting number. We just know the text we want to replace i.e. VBA and number of characters i.e. 3. The FIND function returned the position of VBA in the A3 cell that is used by the REPLACE function.
The demo of using SEARCH and REPLACE function
The difference between FIND and SEARCH functions is that SEARCH function is case-insensitive and you may use wildcard characters there. You may learn more about both functions in the SEARCH/FIND tutorial.
If we had provided “vba” instead of “VBA” in the FIND function, the result would have been:
A #VALUE! Error occurred.
Now, I am just replacing the FIND by SEARCH function and see the output:
The SEARCH/REPLACE formula:
1 =REPLACE(A3,SEARCH("vba",A3),3,"Visual Basic for Applications")
You see, it produced the correct result without any error.
So, we may conclude a few things regarding REPLACE/FIND and SEARCH before proceeding to SUBSTITUTE function:
- If you know the starting position of string to be replaced in existing text, provide that number.
- If you have no idea where it is starting but know the word/character to replace then use the FIND or SEARCH function.
- Use the FIND function with REPLACE If you know the case of letters for the string to replace.
- If doubtful or have no idea then use the SEARCH function with REPLACE.
The Excel SUBSTITUTE function to replace all
Excel has another function for replacing strings in the existing text. The function is called SUBSTITUTE and it is useful if you know the string in the existing text to replace and require to control the replacement occurrences.
By default, the SUBSTITUTE function replaces all occurrences of the existing string by new string.
See the SUBSTITUTE function in action and I will explain:
The SUBSTITUTE formula for replacing all “-“with “”:
The resultant sheet:
You can see, all occurrences of the “-“ are replaced by “” i.e.
457-4575-4574-7 is changed to 457457545747
This is the default behavior of the SUBSTITUTE function to replace all occurrences. What if you want to replace just the specified occurrence?
Have a look at this example:
The formula used in the B5 cell:
In B4 cell:
So, you may specify the occurrence to replace by using the 4th argument in SUBSTITUTE function. In B5, we specified 2 and it returned: 457-45754574-7. So, only replace second “-“ by “”.
So, what are the first three arguments in SUBSTITUTE function? The general syntax of SUBSTITUTE is:
SUBSTITUTE(text, old_text, new_text, [instance_num])
- In our example case, the A3 to A5 specifies the first argument i.e. text. This is where the existing text is searched and replaced. You may use hard-coded or cell reference there. Required.
- Old_text is the existing string in the text. We used “-“ the example. Required.
- New_text is the text string to be replaced with. In our case, we used “”.
- The instance_num is an optional argument. If omitted, the SUBSTITUTE function replaced all occurrences. In our examples, we used 2, 1 and omitted to replacing all occurrences.