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)
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”
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.
There are several other functions to use for date and time, random number generator.
Check other categories for more articles.
