Replace Data With Excel's SUBSTITUTE Function

Instantly replace words, texts, or characters

By
Ted French
Ted French
Writer
Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs.
lifewire's editorial guidelines
Updated on January 29, 2020

The SUBSTITUTE function replaces existing words, text, or characters with new data. Use the SUBSTITUTE function to remove non-printing characters from imported data, replace unwanted characters with spaces, and produce different versions of the same worksheet.

Instructions in this article apply to Excel 2019, 2016, 2013, 2010, 2007; Excel for Microsoft 365, Excel Online, Excel for Mac, Excel for iPad, Excel for iPhone, and Excel for Android.

SUBSTITUTE Function Syntax

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 )
Screenshot of Excel showing use of the SUBSTITUTE function

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 (see row 2 in the image above) or a cell reference to the location of the text data in the worksheet (see rows 3 and 4).
  • Old_text (required): The text to be replaced.
  • New_text (required): The text that will replace Old_text.
  • Instance_num (optional): A number. If this number is omitted, every instance of Old_text is replaced with New_text. If this number is included, the instances of Old_text specified are replaced (see row 5).

Arguments for the SUBSTITUTE function are case sensitive. 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.

Use the SUBSTITUTE Function

Although it is possible to type the entire formula manually into a worksheet cell, another option is to use the Function Arguments dialog box (or the Formula Builder in Excel for Mac) to enter the function and its arguments into a cell. 

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

When you use the dialog box, Excel takes care of separating each argument with a comma and it encloses the old and new text data in quotation marks.

  1. Enter the data for the tutorial in a blank Excel worksheet.

    A screenshot showing sample tutorial data for the SUBSTITUTE function in Excel

  2. Select cell B3 to make it the active cell.

  3. Select Formulas.

  4. Select Text to open the Text functions drop-down list.

  5. Choose SUBSTITUTE in the list to open the Function Arguments dialog box. In Excel for Mac, the Formula Builder opens.

  6. Place the cursor in the Text text box.

  7. Select cell A3 on the worksheet to enter this cell reference.

  8. Place the cursor in the Old_text text box.

  9. Enter Sales. This is the text that will be replaced. There is no need to enclose the text in quotation marks.

  10. Place the cursor in the New_text text box.

  11. Enter Revenue. This text will be substituted for Sales.

    A screenshot showing how to enter the SUBSTITUTE function in Excel

  12. The Instance_num argument is left blank since there is only one instance of the word Sales in cell A3.

  13. Select OK. Except in Excel for Mac, where you select Done.

  14. The text Revenue Report appears in cell B3.

SUBSTITUTE vs. REPLACE Functions

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

Was this page helpful?