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)
  • 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.

How to use 

The REPLACE function replaces characters in a text string by position. The REPLACE function is useful when the location of the text to be replaced is known or can be easily determined.

REPLACE function takes four separate arguments. The first argument, old_text, is the text string to be processed. The second argument, start_num is the numeric position of the text to replace. The third argument, num_chars, is the number of characters that should be replaced. The last argument, new_text, is the text to use for the replacement.

Examples

To replace the "C" in the path below with a "D":

=REPLACE("C:\docs",1,1,"D") // returns "D:\docs"

To replace 3 characters starting at the 4th character:

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

You can use REPLACE to remove text by specifying an empty string ("") for new_text. The formula below uses REPLACE to remove the first character from the string "XYZ":

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

The example below removes the first 4 characters:

=REPLACE("www.google.com",1,4,"") // returns "google.com"

Related functions

Use the REPLACE function to replace text at a known location in a text string. Use the SUBSTITUTE function to replace text by searching when the location is not known. Use FIND or SEARCH to determine the location of specific text.

Notes

  • To remove text, use an empty string ("") for new_text.
  • REPLACE returns #VALUE is start_num or num_chars is not a positive number.
  • REPLACE works on numbers, but the result is text.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.