Documentation/Calc Functions/SUBSTITUTE
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
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:
ODF standard:
Related (or similar) Excel functions:
SUBSTITUTE