The SUBSTITUTE function

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

This is the format of the SUBSTITUTE function:

=SUBSTITUTE(SOURCE TEXT, OLD TEXT, NEW TEXT, INSTANCE NUMBER)

Here is an example of the SUBSTITUTE function:

=SUBSTITUTE(“I can’t remember where I parked the car”, “I”, “She”) = “She can’t remember where I parked the car”

This function is valuable when you are working with a variable string of text that is not consistent in its length or format, but which contains some consistent variables. SUBSTITUTE works best in situations where the exact position of a piece of text is not known, but its existence within the string is.

An example of a functional implementation of this would be converting a template form and personalising it for use with clients. In this scenario the content of the message is not consistent, and will not be in the same location with every iteration, SUBSTITUTE is an effective tool for this type of text manipulation, as the occurrence is guaranteed but its placement is not

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.

=SUBSTITUTE(“This message is intended for Mr. #fn#. Please note that you currently have an outstanding balance on you account.”, “#fn#”, “Jones”)