Spread Formula Reference
SUBSTITUTE
Formula Functions > Functions R to S > SUBSTITUTE

This function substitutes a new string for specified characters in an existing string.

Syntax

SUBSTITUTE(text,old_piece,new_piece,instance)

Arguments

This function has these arguments:

Argument Description
text String or reference to a cell containing the string in which you want to replace characters
old_piece String to be replaced
new_piece New string to use instead of existing string
instance [Optional] Which occurrence of the existing string to replace; otherwise every occurrence is replaced

Remarks

Use this function to replace specific text with other text. Use the REPLACE function to replace a specific number of characters in a specific location with other characters.

Data Types

Accepts string data for the text, old_piece, and new_piece arguments, and numeric data for the instance argument. Returns string data.

Examples

SUBSTITUTE(""Down Trend"",""Down"",""Up"") gives the result Up Trend

SUBSTITUTE(""Feb 1, 1991"",""1"",""2"", 3) gives the result Feb 1, 1992

Version Available

This function is available in product version 1.0 or later.

See Also

FIND | REPLACE | TRIM | Text Functions