Calc SUBSTITUTE function

For a full list of available calculation functions, please read What calculation functions are supported.

Description

The SUBSTITUTE function is used in text processing to replace specific parts of a text string with new text. Use SUBSTITUTE when you want to replace specific text in a text string; use the REPLACE function when you want to replace any text that occurs in a specific location in a text string.

Syntax

SUBSTITUTE(text, old_text, new_text, [instance_num])

The SUBSTITUTE function syntax has the following arguments:

• text: Required. The text or the reference to a data point containing text for which you want to substitute characters.
• old_text: Required. The text you want to replace.
• new_text: Required. The text you want to replace old_text with.
• instance_num: Optional. Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

Example

Errors

For a full list of formula errors, please read Formula errors.

Related functions

• Calc CHAR function
• Calc CLEAN function
• Calc CODE function
• Calc CONCAT function
• Calc DETECTLANGUAGE function