Replace Data with Excel's SUBSTITUTE Function GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office
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 Tweet Share Email Tweet Share Email MS Office Excel Word Powerpoint Outlook 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 ) 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. Enter the data for the tutorial in a blank Excel worksheet. Select cell B3 to make it the active cell. Select Formulas. Select Text to open the Text functions drop-down list. Choose SUBSTITUTE in the list to open the Function Arguments dialog box. In Excel for Mac, the Formula Builder opens. Place the cursor in the Text text box. Select cell A3 on the worksheet to enter this cell reference. Place the cursor in the Old_text text box. Enter Sales. This is the text that will be replaced. There is no need to enclose the text in quotation marks. Place the cursor in the New_text text box. Enter Revenue. This text will be substituted for Sales. The Instance_num argument is left blank since there is only one instance of the word Sales in cell A3. Select OK. Except in Excel for Mac, where you select Done. 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? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Combine the ROUND and SUM Functions in Excel How to Round Numbers Down in Excel With the ROUNDDOWN Function How to Count Data in Selected Cells With Excel's COUNTIF Function How to Use the Round Function in Excel Use the Excel RIGHT Function to Extract Characters Excel SUMIFS: Sum Only Values Meeting Multiple Criteria Use Excel's EOMONTH Function to Add or Subtract Months How to Use the Google Spreadsheets AVERAGE Function How to Use Excel's MROUND Function How to Create an Excel Lookup Formula With Multiple Criteria Round up Numbers in Excel With the ROUNDUP Function How to Use the Excel TRUNC Function Finding the Location of Data With Excel's MATCH Function Learn How to Remove Extra Spaces From Excel Using TRIM How to Use a Dynamic Range in Excel With COUNTIF and INDIRECT How to Use Excel's Clean Function Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies