SUBSTITUTE Function

Replaces part of a text string with a different text string.

Syntax

SUBSTITUTE (text, old_text, new_text [, start_num ][, ignore_case_opt )

Parameters

Name	Required/Optional	Data Type	Description
text	Required	String	The text or the reference to a cell containing text for which you want to substitute characters.
old_text	Required	String	The text you want to replace.
new_text	Required	String	The text you want to use to replace old_text.
start_num_opt	Optional	Numeric	Specifies which occurrences of old_text to replace.
ignore_case_opt	Optional	Boolean	FALSE if case-sensitive; otherwise, TRUE. The default is FALSE.

Return value

String

Remarks

If you specify start_num_opt, only that occurrence of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

Use the SUBSTITUTE function when you want to replace specific text in a text string. If you want to replace text that occurs in a specific location in a text string, use the REPLACE function.

Example

SUBSTITUTE ("1 January 2003", "January", "JAN")

Returns "1 JAN 2003".

SUBSTITUTE ("1 January 2003","january","JAN")

Returns "1 January 2003". No change is made because the text search is case-sensitive.