The REPLACE function replaces a part of a text string with another text string, given an initial position from which the replacement is desired, a number of characters to replace and the text string with which you want to replace the referred characters.
REPLACE(
old_text,
start_num,
num_chars,
new_text
)
- old_text: Text in which you want to make the replacement or name of the column that contains the text strings in which to make the replacement.
- start_num: Initial position of the character string to delete in old_text (the leftmost character occupies position 1).
- num_chars: Number of characters to remove in old_text .
- new_text: String of characters to be inserted instead of the deleted text.
The REPLACE function returns a text string.
Taking into account that the length of the characters to be replaced in the initial text string does not have to coincide with the length of the text string for which the replacement is to be performed, this function is better defined by saying that it removes a fragment of the text string -fragment defined by an initial position and a number of characters- and inserts in that position a new text string: If the string to be inserted is longer than the string to be deleted, the result returned by REPLACE it will be longer than the original text string. And on the contrary: if the text string to be inserted is shorter than the text string to be deleted, the result returned by REPLACE will be shorter than the original text string. The following example removes a 3-character string starting at position 2 and replaces it with a single character:
The starting position must be an integer greater than zero. Otherwise, the function will return an error.
If start_num is greater than the length of old_text, the REPLACE function will append the new string to the end of old_text. Thus, the following example replaces the character that occupies the tenth position by the character "_", when all the original text strings have a length less than 10:
If 0 is specified as the number of characters to replace, the function simply inserts the new text string at the indicated position. The following example replaces a 0-character string starting at position 2 with a "_" character. Note how, simply, this character has been inserted in the indicated position:
If the position to replace is 1 and the number of characters to replace is 0, REPLACE is equivalent to the CONCATENATE function (the same can be said if the position to replace is greater than the length of old_text , since the string to insert is simply concatenated behind old_text).
In any case, the number of characters to replace cannot be a negative number because, in that case, REPLACE will return an error.
The following example uses the SEARCH function to find the first occurrence of the letter "a" in the "Manufacturer" column, and the REPLACE function to replace two characters from that position with "__". The REPLACE function is included as the first argument of the IFERROR function so that, in those cases in which no letter "a" is found (occasions when the SEARCH function is going to return an error), a controlled result is returned (a Blank in this case).
Notice how, in the last row, the letter "a" occupies the last position of the text string, so when replacing it with "__", the length of the string returned by REPLACE increases by one:
