• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

Substitute Function in Excel

Home » Excel » Text and String Functions in Excel » Substitute Function in Excel

By Jyoti Singh Leave a Comment

Substitute Function in Excel

Substitute function in excel is a very useful function which is used to replace or substitute a given text with another text in a given cell, this function is widely used when we send massive emails or messages in a bulk, instead of creating separate text for every user we use substitute function to replace the information.

Excel Substitute Function (Table of Contents)

  • Substitute Function in Excel
  • Formula
  • How to Use Substitute Function?

What is a Substitute Function in Excel?

Substitute function is text function which is used to replace one or more strings with another text string. Substitute function can be useful for when you want to substitute old text with a new text in the given text. For example, if the given text is 99-11-27-11-28 and you want it as 9911271128 then you can use this function to transform this.

The SUBSTITUTE function is as similar to the REPLACE function used it the excel. However, SUBSTITUTE function replaces one or more instances of a substituted text string. Whereas REPLACE function only replaces the text in a specified position/location of a supplied string. Basically, it is used to replace the text strings from imported data.

Substitute Formula in Excel

Substitute Formula in Excel

Formula and Explanation

Substitute function in excel has four parameters three (text, old_text,new_text) are compulsory parameters and one (instance_num) is optional.

Compulsory Parameter:

  • text: It is a text from which we want to substitute some text.
  • old_text: It is text which is going to replace.
  • new_text: It is the text which replaces the old text.

Optional Parameter:

  • [instance_num]: it specifies the occurrence of old_text. if you specify the instance only that instance will have replaced by substitute function otherwise all instances replaced by it.

How to Use Substitute Function in Excel?

Substitute function is very simple and easy to use. Let understand the working of Substitute function by some examples.

Popular Course in this category
Cyber Monday Sale
All in One Excel VBA Bundle (35 Courses with Projects) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
4.9 (1,353 ratings)
Course Price

View Course

Related Courses
VBA Macros CourseAdvanced Excel CourseTableau Certification Course
You can download this Substitute Function Excel Template here – Substitute Function Excel Template

Example #1

In the first example, we have will substitute the “_” with space in the given set of Name data.

Substitute Function Example 1

To get the desired output Tanuj Rajput from Tanuj_Rajput apply the Substitute formula SUBSTITUTE(A2,”_”,” “,1)

Substitute Function Example 1-1

It will replace the first instance of “_” with space and you will get the desired data as output.

Substitute Function Example 1-2

Example #2

In this example, we will replace the first instance of character “a” with “w” in the given dataset of full name by using the substitute function.

Substitute Function Example 2

Let’s apply the =SUBSTITUTE(A2,”a”,”w”,1) formula in the Formula column,

Substitute Function Example 2-1

As shown in the below table and you will get the output in the Output column, shown in the third column.

Substitute Function Example 2-2

Example #3

In this example, we will replace all instances of character “a” with “w” in the given data set

Example 3

By using the substitute formula excel =SUBSTITUTE(B38,”a”,”w”)

Example 3-1

And drag it to in the formula column to the output with no “a” values as shown in the below table.

Example 3-2

Example #4

In this example, we will replace all the spaces with blank from the given set of Full names.

Example 4

Here, we will apply the below substitute formula to achieve this =SUBSTITUTE(I8,” “,””)

Example 4-1

By using this you will get the Output without space as shown in the below table.

Example 4-2

Things to Remember about the Substitute Function in Excel

  • Substitute function is the case-sensitive function.
    • Substitute function considers tanuj and Tanuj as different values, means it can distinguish b/w lower case and upper case.
  • Substitute function does not support wildcard characters i.e. “?” ,“*” and “~” tilde.

Recommended Articles

This has been a guide to Substitute in Excel. Here we discuss how to use Substitute function in excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • VBA Transpose
  • VBA Sub
  • Remove Space in Excel
  • DSUM Function
  • Minimum in Excel
  • TRANSPOSE Function Excel
  • Excel Subtraction Formula
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Filed Under: Excel, Text and String Functions in Excel

Reader Interactions
Leave a Reply Cancel reply

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

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Download Substitute Function Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

CYBER WEEK OFFER - All in One Excel VBA Bundle (35 Courses with Projects) View More