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.
SUBSTITUTE(Text; SearchText; NewText[; Occurrence])
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.
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.
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).
|=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:
Equivalent Excel functions: