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 occurences 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.