WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Substitute Function in Excel

Substitute Function in Excel

By Tanuj KumarTanuj Kumar | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

What Does Substitute Function Do 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.

Syntax

Substitute Formula in Excel

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 a 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 a substitute function; otherwise, all instances are replaced by it.

How to Use Substitute Function in Excel? (with Examples)

You can download this Substitute Function Excel Template here – Substitute Function Excel Template

Example #1

In the first example, we 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)

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

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

  • The substitute function is the case-sensitive function.
    • Substitute function considers tanuj and Tanuj as different values, which 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 the Substitute function in excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • VBA Sub
  • DSUM Function
  • Minimum in Excel
  • Excel Subtraction Formula
  • How to Create an Excel Spreadsheet?
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 >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. 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

WallStreetMojo

Free Excel Course

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

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

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

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

Login

Forgot Password?

WallStreetMojo

Download Substitute Function Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More