Excel 2000 in a Nutshell

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.