Documentation/Calc Functions/SUBSTITUTE

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

Related LibreOffice functions:
REPLACE

REPLACEB

ODF standard:
Section 6.20.21, part 2

Equivalent Excel functions:
SUBSTITUTE