Name
SUBSTITUTE
Synopsis
Use
SUBSTITUTE
to replace specific text within a text string. When you use this
function you can indicate the number of occurrences that you want to
replace. For example, you can replace every occurrence of the text,
or only the second occurrence by specifying a value of
2
for the Instance_Num
argument.
To Calculate
=SUBSTITUTE(Text
,Old_Text
,New_Text
,Instance_Num
)
The Instance_Num
argument is optional. All
other arguments are required for this function.
-
Instance_Num
An integer value that indicates the instance in the text specified by the
Old_Text
argument that you want to replace. If this argument is omitted, the function replaces every occurrence of the specified text.
Example
Figure 18-6 illustrates how SUBSTITUTE is used to
replace specific characters within a text string. In the example, we
told the function to replace the second instance of “c”
with “C.” If we had omitted the value for the
Instance_Num
argument the function would
have replaced the “c” in both locations.
Get Excel 2000 in a Nutshell now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.