Documentation/Calc Functions/REPLACE

    From The Document Foundation Wiki
    Other languages:


    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:

    =REPLACE(Text; Position; Length; New Text)

    is equivalent to:

    =LEFT(Text; Position-1) & New Text & MID(Text; Position+Length; LEN(Text))

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

    Examples:

    Formula Description Returns
    =REPLACE("mouse"; 2; 3; "ic") Here the function removes three characters, starting at character position 2 ("ous"), and replaces them with two characters ("ic"), returning the string "mice". mice
    =REPLACE(D1; D2; D3; D4) where cell D1 contains the string "mouse", cell D2 contains the value 2, cell D3 contains the value 3, and cell D4 contains the string "ic". Here the function again removes three characters, starting at character position 2 ("ous"), and replaces them with two characters ("ic"), returning the string "mice". mice
    =REPLACE("1234567"; 1; 1; "444") Here the function removes one character, starting at character position 1 ("1"), and replaces it with three characters ("444"), returning the string "444234567". 444234567
    =REPLACE(1,234,567; 1; 1; 444) In this example the original and new text strings are passed as numeric arguments, without quotation marks. Here the function again removes one character, starting at character position 1 ("1"), and replaces it with three characters ("444"), returning the string "444234567". 444234567
    =REPLACE("Input string"; 7; 0; "text ") Here the function removes no characters but inserts a five character string ("text ") immediately before character position 7 ("s"), returning the string "Input text string". Input text string
    =REPLACE("LibreOffice"; 120; 3; " Calc") Here the value of the Position argument is an arbitrary number that is greater than the length of the Text argument, so that the five-character string " Calc" is appended to the end of the original text. In this example the value of the Length argument is ignored (although it must be a non-negative number). LibreOffice Calc
    =REPLACE("Hello xxxx!"; 6; 5; "") Here the function removes five characters, starting at character position 6 (" xxxx"), and replaces them with the empty string (effectively deleting them), returning the string "Hello!". Hello!

    Related LibreOffice functions:

    REPLACEB

    SUBSTITUTE

    ODF standard:

    Section 6.20.17, part 2

    Related (or similar) Excel functions:

    REPLACE