Replace Data with Excel's SUBSTITUTE Function

Working on a laptop
Manuel Breva Colmeiro / Getty Images

The SUBSTITUTE function can be used to replace existing words, text, or characters with new data. Use cases for the function include the need to remove non-printing characters from imported data, replacing unwanted characters with spaces, and producing different versions of the same worksheet.

01
of 03

The SUBSTITUTE Function Syntax

Screenshot of Excel showing use of the SUBSTITUTE function

The Substitute Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments. The syntax for the SUBSTITUTE function is:

= SUBSTITUTE ( Text, Old_text, New_text, Instance_num )

The arguments for the function are as follows:

Text: (required) the data containing the text to be replaced. This argument can contain

  • The actual data enclosed in quotation marks — row two in the image above.
  • A cell reference to the location of the text data in the worksheet — row three above.

Old_text: (required) the text to be replaced.

New_text: (required) the text that will replace Old_text.

Instance_num: (optional) a number.

  • If omitted, every instance of Old_text is replaced with New_text.
  • If included, only the instance of Old_text specified, such as the first or third instances, are replaced as seen in rows five and six above.

Arguments for the SUBSTITUTE function are case sensitive, which means if the data entered for the Old_text argument does not have the same case as the data in Text argument cell, no substitution occurs.

02
of 03

Using the SUBSTITUTE Function

Screenshot of Excel showing the Formula Builder

Although it is possible to type the entire formula manually into a worksheet cell, another option is to use the Formula Builder, as outlined in the steps below, to enter the function and its arguments into a cell such as B3.

= SUBSTITUTE(A3, "Sales", "Revenue")

Advantages of using the Formula Builder are that Excel takes care of separating each argument with a comma and it encloses the old and new text data in quotation marks.

  1. Click on cell B3 to make it the active cell.
  2. Click on the Formulas tab of the ribbon menu.
  3. Click on the Text icon on the ribbon to open the Text functions drop-down.
  4. Click on SUBSTITUTE in the list to bring up the Formula Builder.
  5. Click on the Text line.
  6. Click on cell A3 to enter this cell reference.
  7. Click on the Old_text line.
  8. Type Sales, which is the text we want to replace — there is no need to enclose the text in quotation marks.
  9. Click on the New_text line.
  10. Type Revenue, as the text to be substituted.
  11. The Instance argument is left blank since there is only one instance of the word Sales in cell A3.
  12. Click Done to complete the function.
  13. The text Revenue Report should appear in cell B3.
03
of 03

SUBSTITUTE vs. REPLACE Functions

Screenshot of Excel showing SUBSTITUTE Vs. REPLACE

SUBSTITUTE differs from the REPLACE function in that it is used to exchange specific text at any location in the selected data while REPLACE is used to replace any text that occurs at a specific location in the data.