Syntax
REPLACE(old_text,start_num,num_chars,new_text)
REPLACEB(old_text,start_num,num_bytes,new_text)
Definition. The REPLACE() function replaces the old_text string beginning at start_num and with the length indicated by num_chars with the new string in new_text.
Use REPLACEB() for double-byte characters.
Arguments
old_text (required). The string containing the characters you want to replace
start_num (required). The starting position for text replacement
num_char/num_bytes (required). The number of characters/bytes beyond the start position in which to make replacements
new_text (required). The text that will replace the characters in old_text
Background. Use this function to replace either specific characters or a specific string with a fixed length within a string. An example is a list that contains placeholders that you want to replace with fixed or calculated expressions.
REPLACE() differs from the SUBSTITUTE() function, which replaces a string within a piece of text. The REPLACE() function allows you to define which part of the text to search for replacement.
Assume that you want to replace the word paragraph with the § symbol in a list. Cell B25 contains the text Paragraph 3, Sect. 4. The formula
=REPLACE(B25,SEARCH("paragraph",B25),LEN("paragraph"),"§")results in § 3, Sect. 4 (see Figure 8-8).
Assume now that you want to replace the word date with the current date in a list. If cell A2 contains the text Seattle, date, the formula
=REPLACE(A2,SEARCH("date",A2),LEN("date"),TEXT(TODAY(),"MM/DD/YYYY"))results in Seattle, 12/24/2008 on December 24, 2008.