1. Computing

Excel SUBSTITUTE Function Tutorial

By

Substituting New Text for Old
Excel SUBSTITUTE Function

Excel 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

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.

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. Although the argument can contain the actual data enclosed in quotation marks, it can also be a cell reference to the location of the text data in the worksheet.

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 instance - is replaced.

Note: The arguments for the SUBSTITUTE function are case sensitive.

In the image above for example, if the data to be replaced in Old_text is entered as sales rather than Sales the function will not replace the Old_text with the New_text.

Example: Exchanging Text with the SUBSTITUTE Function

As can be seen in the image above, this example will use the SUBSTITUTE function to make two changes to the data:

  • in row 1, replace the word Sales with the word Purchasing
  • in row 2, replace Quarter 1, 2012 with Quarter 2, 2012 by replacing only the first instance of the number 1 (the 1 in the year 2012 is left unchanged)

Entering the Tutorial Data

  1. Enter the following text into cell indicated:
       C1:    Monthly Report - Sales
       C2:    Quarter 1, 2012
    
    

Substituting Purchasing for Sales in Cell D1

  1. Click on cell D1 - 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 C1 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

  9. Click on the New_text line in the dialog box

  10. Type Purchasing, which is the text we want to add

  11. The Instance argument will be left blank, since there is only one instance of the word Sales in cell C1

  12. Click OK to complete the function and close the dialog box

  13. The line of text Monthly Report - Purchasing should appear in cell D1

  14. When you click on cell D1 the complete function =SUBSTITUTE(C1,"Sales","Purchasing") appears in the formula bar above the worksheet

Substituting Quarter 2 for Quarter 1 in Cell D2

  1. Click on cell D2 - to make it the active cell

  2. Repeat steps 2 to 4 above to open the function's dialog box

  3. In the dialog box, click on the Text line

  4. Click on cell C2 to enter the cell reference into the dialog box

  5. Click on the Old_text line in the dialog box

  6. Type the number 1, which is the text we want to replace

  7. Click on the New_text line in the dialog box

  8. Type the number 2, which is the text we want to add

  9. Click on the Instance line in the dialog box

  10. Type the number 1, since we want to replace the first instance of the number one in cell C2

  11. Click OK to complete the function and close the dialog box

  12. The line of text Quarter 2, 2012 should appear in cell D2

  13. When you click on cell D2 the complete function =SUBSTITUTE(C2,1,2,1) appears in the formula bar above the worksheet

©2014 About.com. All rights reserved.