REPLACE Function Examples – Excel, VBA, & Google Sheets

This Tutorial demonstrates how to use the Excel REPLACE Function in Excel to replace text in a cell.

Replace Main Function

REPLACE Function Overview

The REPLACE Function Replaces a part of a text string, defined by it’s location within the cell, with a new text string.

To use the REPLACE Excel Worksheet Function, select a cell and type:

replace formula syntax

(Notice how the formula inputs appear)

REPLACE function Syntax and inputs:

old_text – The original string of text.

start_num – The character number from which to start replacing.

num_chars – The number of characters you wish to replace.

new_text – The replacement text.

AutoMacro - VBA Code Generator

What is REPLACE?

The REPLACE function in Excel is used to replace 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 Ex 01

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

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

Since REPLACE takes numeric arguments for character position and length of characters to be replaced, we can use additional functions to enhance the REPLACE function.

For example, what if we do not know exactly what character position the replace should start at? 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 Ex 02

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. In most cases, these can be used interchangeably however, the following rule of thumb applies.

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.

Replace Ex 04

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 Ex 05

Interested in More Text functions?

See our other articles for more on SUBSTITUTE, or how other text functions in Excel such as LEFT, MID and RIGHT are used.

REPLACE in Google Sheets

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

Replace Google Function

Additional Notes

Use the REPLACE Function when you want to replace text based on it’s location, regardless of content. You may want to use Excel’s FIND Function or Excel’s SEARCH Function to determine the start location. You may also wish to use the LEN Function to count the number of characters to replace.

Use the Substitute Function to find and replace specific strings of text, ignoring location.

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.

Return to the List of all Functions in Excel

 

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!