Excel: Substitute Function

In Excel, the Substitute function replaces a set of characters with another.

The syntax for the Substitute function is:

Substitute( text, old_text, new_text, nth_appearance )

text is the original string to use to perform the substitution.

old_text is the existing characters to replace.

new_text is the new characters to replace old_text with.

nth_appearance is optional. It is the nth appearance of old_text that you wish to replace. I f this parameter is omitted, then every occurrence of old_text will be replaced with new_text.

Applies To:

Excel 2007, Excel 2003, Excel XP, Excel 2000

For example:

Let's take a look at an example:

Based on the Excel spreadsheet above:

=Substitute(A1, "bet", "con", 1) would return "Alphacon soup"

=Substitute(A2, "t", "4", 2) would return "techon4henet"

=Substitute(A2, "t", "4") would return "4echon4hene4"