REPLACE Function Examples – Excel, VBA, & Google Sheets

This tutorial demonstrates how to use the REPLACE Function in Excel and Google Sheets to replace text in a cell.

What is REPLACE?

The REPLACE Function replaces a specified number of characters in a text string with a different specified text string.

Let us look at the following example replacing the string “quick brown” with “slow white”.

=REPLACE(C2,5,11,C4)

The REPLACE function replaces 11 characters starting at character 5 in C2 with the string in C4.

Note: The replace function doesn’t “find and replace” like the SUBSTITUTE Function. Instead it replaces text at a specified position.

How to use REPLACE

The REPLACE function takes four required arguments:

• Old_text: Text where you want to replace characters
• Start_num: The first character number in Old_text that you want to replace
• Num_chars: The number of characters from Start_num you want to replace
• New_text: The text that will replace the specified characters in Old_text

It is important to note that spaces are counted as characters in Excel which is why the “q” in “quick” starts at character 5 and not 4 and “quick brown” is 11 characters long instead of 10.

Dynamic REPLACE

Often, the start_num and/or num_chars will be dynamic. So we can use other functions to populate those arguments.

For example, the FIND or SEARCH functions can be used to find the position number of a character in a string to start the replace at. Note that FIND is case sensitive and SEARCH is not. Since we do not need to be case sensitive in this example, we will use SEARCH.

We can then use the LEN Function to determine how many characters are in the string and feed that value to the Num_chars argument in REPLACE.

Using =SEARCH(C3,C2,1) to find the character position of “quick brown” and,

using =LEN(C3) to find the length of character of “quick brown” as arguments in REPLACE:

=REPLACE(C2,SEARCH(C3,C2,1),LEN(C3),C4)

The above formula references the results of the SEARCH and LEN functions instead of hard coding numbers as arguments for REPLACE. If we want to replace a different part in the original string, we can simply make the following changes and the SEARCH/LEN functions will handle the necessary adjustments.

 

Difference Between REPLACE and SUBSTITUTE

A similar function to REPLACE is SUBSTITUTE.

The REPLACE function should be used when the position of the text characters to be replaced in the string are known, or when a part of a word or string is being replaced. For example, removing a hyphen from a string of numbers (of course the SUBSTITUTE Function would work here as well).

=REPLACE(B3,4,1,"")

The SUBSTITUTE function should be used when the string of text to be replaced is known, or a whole word or words are being replaced, like our fox example used earlier.

REPLACE in Google Sheets

The REPLACE Function works exactly the same in Google Sheets as in Excel:

REPLACE Examples in VBA

You can also use the REPLACE function in VBA. Type:

application.worksheetfunction.replace(old_text,start_num,num_chars,new_text)

For the function arguments (old_text, etc.), you can either enter them directly into the function, or define variables to use instead.