Jump to content

Documentation/Calc Functions/SUBSTITUTE

From The Document Foundation Wiki


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