Replaces a part of the string passed to the function with a new string. A start position and the number of characters to be replaced are also passed to the function. The function uses zero-based indexing.
= REPLACE(orig_str, startIndex, length, new_str)
orig_str |
A value that contains the characters which have to be replaced. The value can be specified as a decimal value (e.g., 37.48), as a string (“Word”), as the value of a variable (e.g, FirstName), or as a Date (“02/19/2009”) |
startIndex |
A positive integer which specifies the start position from which characters have to be extracted. |
length |
A non negative integer which specifies the number of characters to be extracted. |
new_str |
The string which would replace the characters in the original string. The value can be specified as a decimal value (e.g., 37.48), as a string (“Word”), as the value of a variable (e.g, FirstName), or as a Date (“02/19/2009”) |
String
In the case of an error, the function displays an error message: “Replace(orig_str, startIndex, length, new_str): Error message”
The function can be used in combination with other functions.
Example |
Returns |
= REPLACE(“This is good”, 8, 4, “bad”) |
“This is bad” |
= REPLACE("$1,234", 0, 1, "£") |
“£1,234” |
= REPLACE(ShippedDate, 6, 2, ““) |
The ShippedDate with the year having 2 digits instead of 4 digits. |
= UPPER(REPLACE(“This is good”, 8, 4, “bad”)) |
The upper case version of the string which is a result of replacement. |
= CONCATENATE(“Ship year: “, REPLACE(ShippedDate, 5, 2, ““)) |
A concatenated version of the extracted string with the passed string. |