SUBSTITUTE
Name
Substitute
Description
Substitutes a new string for specified characters in an existing string.
Syntax
SUBSTITUTE(Text, Old_text, New_text, Instance)
Remarks
Arguments are as follows:
| Argument | Description |
|---|---|
| Text | The string or a reference to a cell containing the string in which you want to replace characters. |
| Old_text | String to be replaced. |
| New_text | New string to use instead of existing string. |
| Instance | (Optional) Specifies which occurrence of the existing string to replace. If you specify a value, only that instance of the existing string is replaced. Otherwise, every occurrence of the existing string is replaced in the text specified by the Text argument. |
Use the SUBSTITUTE 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 Type
Accepts string data for the Text argument, string data for the Old_text argument, string data for the New_text argument, and numeric data for the Instance argument. Returns string data. For more information, see Data Type for Each Cell Type.
Example
fpSpread1.Formula = "SUBSTITUTE(""Sale Trend"",""Sale"",""Cost"")" equals "Cost Trend"
fpSpread1.Formula = "SUBSTITUTE(""Feb 1, 1991"",""1"",""2"", 3)" equals "Feb 1, 1992"