Documentation/Calc Functions/SUBSTITUTE

    From The Document Foundation Wiki
    Other languages:


    Function name:

    SUBSTITUTE

    Category:

    Text

    Summary:

    Searches a supplied text string for a specified sub-string, replacing one or all occurrences of that sub-string with a new sub-string. The search is case-sensitive.

    Syntax:

    SUBSTITUTE(Text; SearchText; NewText[; Occurrence])

    Returns:

    Returns a text string that corresponds to the original text string, but with one or all occurrences of the search sub-string replaced by a new sub-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 substituted.

    SearchText is a text string (in quotation marks), a number, or a reference to a cell containing one of those types, that is the sub-string to be searched for within Text.

    NewText is a text string (in quotation marks), a number, or a reference to a cell containing one of those types, that is the sub-string to replace one or all occurrences of SearchText within Text. NewText and SearchText can have different lengths.

    Occurrence is a positive integer, or a reference to a cell containing a positive integer, that specifies which occurrence of SearchText to replace (counting from the left). If Occurrence is omitted, all occurrences of SearchText are replaced.

    • If Occurrence is non-numeric, then SUBSTITUTE reports a #VALUE! error.
    • If Occurrence is a non-integer value, then SUBSTITUTE truncates it to an integer value.
    • If Occurrence is less than 1, then SUBSTITUTE reports an invalid argument error (Err:502).
    • If there are no matches of SearchText within Text, or the match specified by a value of Occurrence does not exist, then SUBSTITUTE returns a copy of Text.

    Additional details:

    You can pass the Text, SearchText, and NewText arguments as numbers but SUBSTITUTE 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
    =SUBSTITUTE("colour flavour humour labour"; "our"; "or") Here all occurrences of the sub-string "our" are replaced by "or" (converting British spellings to US spellings). "color flavor humor labor"
    =SUBSTITUTE (D1; D2; D3; D4) where cells D1 to D4 contain the values 123123123, 3, 77, and 2 respectively. Here the second occurrence of the digit 3 is replaced by the digits 77, with other occurrences of 3 unchanged. "1231277123"
    =SUBSTITUTE("spreadsheet"; "zzz"; "?") Here the string "zzz" does not appear in "spreadsheet" and so no substitutions are made. A copy of the original string is returned unchanged. "spreadsheet"
    =SUBSTITUTE("spreadsheet"; "ad"; "?"; 2) Here the string "ad" appears in "spreadsheet" only once, so there is no second match and no substitutions are made. Again, a copy of the original string is returned unchanged. "spreadsheet"

    Related LibreOffice functions:

    REPLACE

    REPLACEB

    ODF standard:

    Section 6.20.21, part 2

    Related (or similar) Excel functions:

    SUBSTITUTE