Exceljet

Quick, clean, and to the point

Excel REPLACE Function

Excel REPLACE function
Summary 

The Excel REPLACE function replaces characters specified by location in a given text string with another text string. For example =REPLACE("XYZ123",4,3,"456") returns "XYZ456".

Purpose 
Replace text based on location
Return value 
The altered text.
Syntax 
=REPLACE (old_text, start_num, num_chars, new_text)
Arguments 
  • old_text - The text to replace.
  • start_num - The starting location in the text to search.
  • num_chars - The number of characters to replace.
  • new_text - The text to replace old_text with.
Usage notes 

The REPLACE function is useful for replacing text at a known location in a given string. For example, the following formula replaces 4 characters starting at the 3rd character:

=REPLACE("XYZ123",4,3,"456") // returns "XYZ456"

You can use REPLACE to remove text by specifying an empty string ("") as new text. In the formula below, we use REPLACE to remove the first character:

=REPLACE("XYZ",1,1,"") // returns "YZ"

Excel has several functions that can be used to replace text. Below is a brief summary. Also see linked examples below.

  • Use the REPLACE function when you want to replace text based on a known location.
  • Use FIND or SEARCH to find and replace text when the location is not known in advance.
  • Use SUBSTITUTE to replace one or more instances of text based on content only.