REPLACE

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.

Syntax

= REPLACE(orig_str, startIndex, length, new_str)

Parameters

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”)

Return Type

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.

Examples

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.