Documentation/Calc Functions/REPLACE

Function name:
REPLACE

Category:
Text

Summary:
Replaces characters in a text string with the characters of a different text string.

Syntax:
REPLACE(Text; Position; Length; New Text)

Returns:
Returns a text string that corresponds to the original text string but with the specified characters replaced by the supplied new text string.

Arguments:
Text is a text string (in quotation marks), a number, or a reference to a cell containing one of those types, that is the original string containing characters to be replaced.

Position is a positive integer, or a reference to a cell containing a positive integer, that is the position within Text of the first character to be replaced.

Length is a non-negative integer, or a reference to a cell containing a non-negative integer, that is the number of characters within Text to be replaced.

New Text is a text string (in quotation marks), a number, or a reference to a cell containing one of those types, that is the new string containing the replacement characters. It is not necessary for the length of New Text to be the same as the value of Length. Set New Text to the empty string to delete rather than replace characters in Text.
 * If either Position or Length is non-numeric, then REPLACE reports a #VALUE! error.
 * If either Position or Length is a non-integer value, then REPLACE truncates it to an integer value.
 * If Position is less than 1 or Length is less than 0, then REPLACE reports an invalid argument error (Err:502).
 * If Length is equal to 0, no characters are replaced. However, the characters of New Text are inserted before the character that is originally at the position given by Position.
 * If the value of Position is greater than the length of Text, the characters of New Text are appended at the end of the characters originally in Text.
 * If Position + Length is greater than the length of Text, REPLACE replaces all characters from Position to the end of Text.

Additional details:
The formula:

is equivalent to:

You can pass the Text and New Text arguments as numbers but REPLACE always returns text. If you intend to perform further calculations on a number that has been converted to text, you will need to convert it back to a number (for example, using either the VALUE function or the NUMBERVALUE function).

Related LibreOffice functions:
REPLACEB

SUBSTITUTE

ODF standard:
Section 6.20.17, part 2

Equivalent Excel functions:
REPLACE