The function REPLACE takes a base string and replaces the contents at particular location – as defined by indexing the base string – with another string.
This function takes four pieces of input: two strings and two numbers. The first string designates the base string, and the second designates the string to be inserted into the base string. The first number designates the beginning position of the substring to be cleaved and replaced within the base string, and the second number designates the end position of said substring. This function outputs a single string: the result of the second string being inserted into the base string at the given location.
Declaration
REPLACE(base_string, inserted_string, start, end) -> replaced_string
Parameters
base_string (required, type: string)
The base string.
inserted_string (required, type: string)
The string to insert into the base string.
start (required, type: number)
The index number indicating at which character within the base string replacement will begin. Insertion of the inserted string begins before the character currently occupying the designated location in the string. Indexing count begins at zero.
end (required, type: number)
The index number indicating which character in the base string marks the end of the substring to be cleaved and replaced. The character occupying this location will not be cleaved itself. Indexing count begins at zero.
Return Values
replaced_string (type: string)
The string that results from taking the base string and replacing the designated characters with the inserted string.
Examples
The following example takes the string "Replace things in this string", cleaves everything from the ninth character to the fifteenth character, and replaces them with the string "noodles." Note that the ninth character in the string is indexed 8 and the fifteenth character in the string is indexed 14; this is because indexing starts at zero. The first character – "R" – would be at index 0. Also note that the string "noodles" is one character longer than the string being cleaved and replaced, and this causes no problems. There is no character limit (or minimum, for that matter) on what can be inserted after the designated substring is removed:
REPLACE("Replace things in this string", "noodles", 8, 14) -> "Replace noodles in this string"
The following example cleaves only a single character from the string "Replace things in this string" and replaces it with the much longer string "noodles". Note that the second character within the the base string – "e" – is indexed 1, and that this is the precise character that is removed. The character occupying the location indexed end is not cleaved, and so the third character in the base string – "p" – remains in the outputted string, the ninth character in the resulting string:
REPLACE("Replace things in this string", "noodles", 1, 2) -> "Rnoodlesplace things in this string"
To cleave and replace a portion of a base string that begins with the first character, use the index number 0. The following example takes the string "Replace things in this string", and replaces everything from the first to the fifteenth character with the string "noodles". Note that what was the fifteenth character in the base string is now the eighth:
REPLACE("Replace things in this string", "noodles", 0, 14) -> "noodles in this string"
It is not absolutely necessary for the index numbers to call upon an existing portion of the base string in order to return a valid result. The following example takes the string "Replace things in this string" and attempts to cleave everything from the ninth character to the 1001st. There aren't a hundred characters in this base string, let alone a thousand, but the REPLACE function simply cleaves what does exist between the given indexes:
REPLACE("Replace things in this string", "noodles", 8, 1000) -> "Replace noodles"
Examples
While the REPLACE function is good for replacing subcomponents of strings when you know exactly the location within the string you want to cleave and replace, it does not provide a means of replacing multiple substrings at once or defining substrings by their components rather than their precise location. For that functionality, the SUBSTITUTE function is required.
Due to subtleties in how the REPLACE function works under the hood, it can actually go beyond replacing subsections of a base string. What the REPLACE function does, in practice, is begin transcribing the base string, stop when it reaches the index start, insert the string to be inserted, and then resume transcription of the base string, starting at the index end. This means that, in addition to replacing chunks of a base string, the REPLACE function can also be used to insert characters into a string without removing anything in the process. In the following example, startand end are the same number, allowing "and more" to be appended to the end of the string "Replace things in this string":
REPLACE("Replace things in this string", " and more", 29, 29 -> "Replace things in this string and more")
This also allows for repetition of early parts of the base string, if the index end to designate a position in the base string before start does. The following example transcribes the base string "Replace things in this string" until it gets to the ninth character, inserts the string "noodles", and transcribes the entire base string from the beginning:
REPLACE("Replace things in this string", "noodles ", 8, 0) -> "Replace noodles Replace things in this string"