REPLACE(), REPLACEB()

Syntax

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

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

Replacing the word paragraph with §.

Figure 8-8. Replacing the word paragraph with §.

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.

See Also

MID(), SEARCH(), SUBSTITUTE(), TRIM()