The REPLACE function

The REPLACE function is a text function which locates and replaces a specified text string with a defined second string. Very similar in function to SUBSTITUTE, the difference between these functions is that REPLACE will locate the set of characters requested and replace those with the defined text, whereas SUBSTITUTE will locate the first instance of the requested string and replace it.

This is the format of the REPLACE function:

=REPLACE(OLD TEXT, START CHARACTER, NUMBER OF CHARACTERS, NEW TEXT)

Here is an example of the REPLACE function:

=REPLACE(“The dog went to play outside”, 5, 3, “child”) = “The child went to play outside”

This function is valuable when you have defined information within a string or set of strings which you need to eliminate. Using variables this is a great technique for dynamically inserting names, addresses, or codes into an otherwise generic text string.

An example of a functional implementation of this would be converting a templated form and personalizing it for use with clients. Given that the information will consistently be in the same location of the text, REPLACE is an effective tool for this type of text manipulation.

ex: You have tagged your text with variables you want to import such as #fn# for the individuals first name, or #ln# for the individuals last name.

=REPLACE(“Dear #fn#, we would like to thank you for…”, 6, 4, “Tim”)

“Dear Tim, we would like to thank you for…”