SUBSTITUTE
The function SUBSTITUTE finds instances of a particular substring within a base string and replaces them with a new substring.
This function takes three strings as input: a base string, an old string, and a new string. It outputs a single string: the base string, but with occurrences of the old string replaced with the new. As an optional piece of input, it will also accept a number, which indicates which occurrence of the old string within the base string will be replaced. If a number is given as input, SUBSTITUTE will make only a single substitution.
Declaration
Parameters
base_string (required, type: string) The base string.
old_string (required, type: string) The string within the base string to be found and replaced.
new_string (required, type: string) The string to replace the old string with.
occurrence(optional, type: number) The designation of which occurrence of the old string within the base string will be replaced. It does not have a value by default: if no value is given for occurrence, the SUBSTITUTE function will replace all instances of the old string with the new string. However, if a value is given for occurrence, then only the occurrence of the old string corresponding with the given number will be replaced. Indexing count begins at zero.
Return Values
final_string (type: string) The string that results from taking the base string and replacing instances of the old string with the new string.
Examples
The following example takes the string "These words are words", searches it for occurrences of the string "words", and replaces each of them with the string "noodles". Note that both occurrences of "words" within "These words are words" are replaced.
In order to replace only a single occurrence of "words" within "These words are words", use the optional variable occurrence to designate which "words" will be replaced. Note that indexing begins at 0, so setting occurrence = 1 replaces the second occurrence of "words" within "These words are words":
If occurrence indicates an instance within the base string that does not exist, the output of the SUBSTITUTE function is simply the unmodified base string. In the following example, occurrence = 2, meaning that the SUBSTITUTE function will attempt to replace the third occurrence of the string "words" within "These words are words." There is no such occurrence – there are only two instances of "words" within that base string – and so the output returns the base string unmodified:
This is the output SUBSTITUTE will return any time it is called upon to make a substitution it is impossible to make. The same thing happens if you attempt to replace a string that does not exist within the base string. This occurs in the following example, which searches for all instances of the string "coconuts" within "These words are words" and attempts to replace them with the string "noodles". Nowhere in "These words are words" does the string "coconuts" appear, so the output returns the base string:
This same behavior occurs for the same reasons if the old string is longer than the base string. There is no way for such instances to exist when the relevant substring is longer than the base string, and so the output in such cases will always be the unmodified base string:
Note that no such length constraint applies to the new string. The following example takes all instances of the string "words" within the string "These words are words" and replaces them with the string "These words are words and more". Replacing parts of the base string with strings that are longer than the original base string is perfectly valid:
Discussion
While the SUBSTITUTE function is good for replacing subcomponents of strings when you already know what and which subcomponents you want to replace, it does not provide fine-grained control over exactly where in the base string the new string is inserted or precisely the location of the removed substring. For replacement based on string index as opposed to content, the REPLACE function is required.
Last updated