Formulas and Functions

SUBSTITUTE
The SUBSTITUTE function returns a string where the specied characters of a given
string have been replaced with a new string.
SUBSTITUTE(source-string, existing-string, new-string, occurrence)
 source-string: A string. source-string is a string value.
 existing-string: The string within the given string that is to be replaced. existing-
string is a string value.
 new-string: The text used as a replacement for the section of the given string that
is replaced. new-string is a string value. It does not have to be the same length as
the text replaced.
 occurrence: An optional value specifying the occurrence that should be replaced.
occurrence is a number value and must be greater than or equal to 1, or omitted.
If greater than the number of times existing-string appears within source-string, no
replacement will occur. If omitted, all occurrences of existing-string within source-
string will be replaced by new-string.
Usage Notes
You can replace individual characters, whole words, or strings of characters within Â
words.
Examples
=SUBSTITUTE(”a b c d e f”, “b”, “B”) returns “a B c d e f”.
=SUBSTITUTE(”a a b b b c”, “a”, “A”, 2) returns “a A b b b c”.
=SUBSTITUTE(”a a b b b c”, “b”, “B”) returns “a a B B B c”.
=SUBSTITUTE(”aaabbccc”, “bc”, “BC”, 2) returns “aaabbccc”.
Related Topics
For related functions and additional information, see:
“REPLACE” on page 318
“Listing of Text Functions” on page 306
“Value Types” on page 36
“The Elements of Formulas” on page 15
“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26
“Pasting from Examples in Help” on page 41
322 Chapter 11 Text Functions