Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Find closest match in Excel
  • How to use Excel ROWS Function
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • Offset in Excel
  • Multi-criteria lookup and transpose in Excel

Data Analysis Examples

  • How to Create Column Chart in Excel
  • How To Create Pareto Chart in Excel
  • Remove Duplicates Example in Excel
  • Conditional Formatting New Rule with Formulas in Excel
  • Excel Frequency Function Example

Data Validation Examples

  • Excel Data validation must begin with
  • Excel Data validation date in next 30 days
  • Excel Data validation only dates between
  • Excel Data validation must contain specific text
  • Prevent invalid data entering in specific cells

SUBSTITUTE function: Description, Usage, Syntax, Examples and Explanation

by

 What is SUBSTITUTE function in Excel?

SUBSTITUTE function is one of TEXT functions in Microsoft Excel that substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.

Syntax of SUBSTITUTE function

SUBSTITUTE(text, old_text, new_text, [instance_num])

The SUBSTITUTE function syntax has the following arguments:

  • Text:  The text or the reference to a cell containing text for which you want to substitute characters.
  • Old_text: The text you want to replace.
  • New_text:  The text you want to replace old_text with.
  • Instance_num(Optional): Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.
Worked Example:   Extract word that begins with specific character in Excel

Example of SUBSTITUTE function

Steps to follow:

Worked Example:   Convert text to numbers using VALUE function in Excel

1. Open a new Excel worksheet.

2. Copy data in the following table below and paste it in cell A1

Note: For formulas to show results, select them, press F2 key on your keyboard and then press Enter.

You can adjust the column widths to see all the data, if need be.

Worked Example:   How to get last word in a cell in Excel
Data
Sales Data <
Quarter 1, 2008 <
Quarter 1, 2011 <
Formula Description (Result) Result
=SUBSTITUTE(A2, “Sales”, “Cost”) Substitutes Cost for Sales (Cost Data) Cost Data
=SUBSTITUTE(A3, “1”, “2”, 1) Substitutes first instance of “1” with “2” (Quarter 2, 2008) Quarter 2, 2008
=SUBSTITUTE(A4, “1”, “2”, 3) Substitutes third instance of “1” with “2” (Quarter 1, 2012) Quarter 1, 2012

Post navigation

Previous Post:

CHAR function: Description, Usage, Syntax, Examples and Explanation

Next Post:

SEARCH, SEARCHB functions: Description, Usage, Syntax, Examples and Explanation

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Clean and reformat telephone numbers using SUBSTITUTE function in Excel
  • CHAR function: Description, Usage, Syntax, Examples and Explanation
  • CODE function: Description, Usage, Syntax, Examples and Explanation
  • Remove leading and trailing spaces from text in one or more cells in Excel
  • Find function vs Search function in Excel
  • DATEDIF function: Description, Usage, Syntax, Examples and Explanation
  • WORKDAY.INTL function: Description, Usage, Syntax, Examples and Explanation
  • MONTH function: Description, Usage, Syntax, Examples and Explanation
  • TIMEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • Convert text to date in Excel
  • PV function: Description, Usage, Syntax, Examples and Explanation
  • NPER function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate annuity for interest rate in excel
  • ACCRINTM function: Description, Usage, Syntax, Examples and Explanation
  • COUPPCD function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles