REPLACE Function

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.