Excel SUBSTITUTE Function

01
of 01

Substituting Old Text for New

Substitute or Change Characters with Excel's SUBSTITUTE Function
Substitute or Change Characters with Excel's SUBSTITUTE Function. © Ted French

SUBSTITUTE Function Overview

The SUBSTITUTE function can be used to replace existing words, text, or characters with new data.

Note: The results of the function must appear in a different location than the original text.

Uses for the function include:

  • Removing non-printing characters from imported data;
  • Replacing unwanted characters with spaces;
  • Producing different versions of the same worksheet.

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:

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 - rows five and six above.

Case Sensitivity

The 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.

For example, in row four of the image above, the function views Sales (cell A4) as different from sales (Old_text argumentand, therefore, does not substitute Revenue in as New_text.

Entering the SUBSTITUTE Function

Although it is possible to type the entire formula such as

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

manually into a worksheet cell, another option is to use the function's dialog box - as outlined in the steps below - to enter the function and its arguments into a cell such as B3.

Advantages of using the dialog box 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 list
  4. Click on SUBSTITUTE in the list to bring up this function's dialog box
  5. In the dialog box, click on the Text line
  6. Click on cell A3 to enter this cell reference into the dialog box
  7. Click on the Old_text line in the dialog box
  8. Type Sales, which is the text we want to replace - no need to enclose the text in quotation marks;
  9. Click on the New_text line in the dialog box
  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 OK to complete the function and close the dialog box;
  13. The text Revenue Report  should appear in cell B3;
  14. When you click on cell B3 the complete function
    =SUBSTITUTE(A3, "Sales", "Revenue")
    appears in the formula bar above the worksheet

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.

Format
mla apa chicago
Your Citation
French, Ted. "Excel SUBSTITUTE Function." ThoughtCo, Oct. 23, 2016, thoughtco.com/excel-substitute-function-tutorial-3123795. French, Ted. (2016, October 23). Excel SUBSTITUTE Function. Retrieved from https://www.thoughtco.com/excel-substitute-function-tutorial-3123795 French, Ted. "Excel SUBSTITUTE Function." ThoughtCo. https://www.thoughtco.com/excel-substitute-function-tutorial-3123795 (accessed September 24, 2017).