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.
Version 
Usage notes 

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.