How to Replace Text by Excel REPLACE and SUBSTITUTE

The Excel REPLACE and SUBSTITUTE functions can be used to replace 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 the REPLACE function

The REPLACE function takes four arguments that are required.

The syntax of using the REPLACE function:

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 the word “MS” with “Microsoft” by using the Excel REPLACE function:

The REPLACE formula used in the example:

=REPLACE(“MS Excel is cool”,1,2,”Microsoft”)

The replaced text is: “Microsoft Excel is cool”

On that example basis, let me explain the arguments of the REPLACE function:

Argument	Description
Existing_text	Existing_text is required. In the example, “MS Excel is cool” is the existing text where you want to use the REPLACE function.
start_number	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 text. In the context of our example, the start position is the first letter i.e. “M”.
num_chars	The third argument is num_chars which specifies how many characters to replace in the existing text. I used 2 in the REPLACE formula. So replace “MS” with new_string.
New_text	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.

Tip: The REPLACE function is used when you need to replace text based on its locations. If you do not know the position then use FIND/SEARCH functions to locate the position or use SUBSTITUTE function.

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:

=REPLACE(A3,7,3,”Visual Basic for Applications”)

The result:

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 the REPLACE function. See the example below.

The FIND and REPLACE formula:

=REPLACE(A3,FIND(“VBA”,A3),3,”Visual Basic for Applications”)

The result:

You can see that we did not provide an actual numeric value for the starting number. We just know the text we want to replace i.e. VBA and the 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 the FIND and SEARCH functions is that the SEARCH function is case-insensitive and you may use wildcard characters there.

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 with the SEARCH function and see the output:

The SEARCH/REPLACE formula:

=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 with the new string.

See the SUBSTITUTE function in action and I will explain:

The SUBSTITUTE formula for replacing all “-“with “”:

=SUBSTITUTE(A3,”-“,””)

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:

=SUBSTITUTE(A5,”-“,””,2)

In B4 cell:

=SUBSTITUTE(A4,”-“,””,1)

So, you may specify the occurrence to replace by using the 4^th argument in the SUBSTITUTE function. In B5, we specified 2 and it returned: 457-45754574-7. So, only replace the second “-“ by “”.

So, what are the first three arguments in the SUBSTITUTE function?

The general syntax of SUBSTITUTE is:

SUBSTITUTE(text, old_text, new_text, [instance_num])

Where:

Argument	Description
text	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 references there. Required.
Old_text	Old_text is the existing string in the text. We used “-“ in the example. Required.
New_text	New_text is the text string to be replaced with. In our case, we used “”.
instance_num	The instance_num is an optional argument. If omitted, the SUBSTITUTE function replaces all occurrences. In our examples, we used 2, 1, and omitted to replace all occurrences.

An example of the REPLACE function

The example of REPLACE in cell

The example of using FIND function with REPLACE

The demo of using SEARCH and REPLACE function

The Excel SUBSTITUTE function to replace all

Learn More: