In Excel, the SUBSTITUTE function finds a string in a cell and replaces it with another string. The SUBSTITUTE function is case-sensitive and looks for instances of the string in the cell.

You can choose whether the SUBSTITUTE function works on a specific instance of the string or if it affects all instances. This replaces, or substitutes, all occurrences of the old string with the new one.

SUBSTITUTE Function Syntax

        =SUBSTITUTE(text, old_text, new_text, [instance_num])
    

• text: Either a string itself or a target cell where you want the function to look.
• old_text: The string that you want to replace.
• new_text: The new string which will take the place of the old one.
• [instance_num]: The instance number of the old string that you want to replace. Leave blank if you want all instances to be replaced.

Even though Excel's official syntax uses the term text rather than string, you can use the function on both numbers and symbols. If you want to remove a character, you can simply substitute it with blank.

Related: How to Use IF Function With Nested Formulas in Excel

How to Use the SUBSTITUTE Function

Nothing better than an example to see the SUBSTITUTE function in use. In this particular example, we have a phone number that has the wrong country code (+98). The goal is to change this country code to the correct one (+1) using the SUBSTITUTE function.

1. Select the cell that you want the substituted string to appear. That would be A2 for this example.
2. In the formula bar, enter the formula below and press Enter:

           =SUBSTITUTE(A2, "98", "1", 1)
       

   This will look inside cell A2, find the string 98, and replace it with 1. The instance number of 1 in the final part of the formula expresses that only the first 98 should be replaced.
3. Excel will now change the first 98 to a 1, changing the country code for the phone number. You can go ahead and remove the instance number and observe how the number will change, as every 98 is replaced with 1.

Nested Substitutions

The SUBSTITUTE function can't substitute a bunch of strings in a single cell. However, you can have three separate substitutions performed on the same cell. A good way to achieve this is to nest the functions inside each other.

In this example, we have a cell containing three abbreviations. The goal is to replace them with the full names they represent.

1. Select the cell where you want the output to appear. C2 in this example.
2. In the formula bar, enter the formula below:

           =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "T", "Terry"), "F", "Frank"), "J", "John")
       

   Since the innermost function looks inside cell A2, the two others will look inside A2 as well.
3. Press Enter. Excel will now replace the abbreviation with the full names.

Now You Can Use the SUBSTITUTE Function

The SUBSTITUTE function gives you the ability to conveniently replace characters in strings. You can also determine which incidence of the string you want to replace.

The function also allows you to replace strings with blanks, or to replace blanks with strings. You can also use the Find and Replace Search feature in Excel to find and replace things.