WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Substring in Excel

Substring in Excel

SUBSTRING Function in Excel

Substring function is  a pre-built integrated function in excel that is categorized under TEXT function. Substring means extracting a string from a combination of string, for example, we have a string as “I am a Good Boy” and  we want to extract Good from the given string in such scenarios we use extracting substring, there is no inbuilt function to extract a substring in excel but we use other functions such as Mid function or left and right function.

Substring in Excel

3 Types of Substring Functions

  1. LEFT Substring Function
  2. RIGHT Substring Function
  3. MID Substring Function

Let us discuss each one of them in detail.

#1 – LEFT Substring Function

It extracts a given specific number of characters from the left side or first character of a supplied text string.

The syntax or formula for the LEFT function in Microsoft Excel is:

LEFT Formula

The LEFT function syntax or formula has the below-mentioned arguments:

  • text: (Compulsory or required parameter) It is the text string that contains the characters where you want to extract
  • num_chars: (Optional parameter) It is the number of characters from the left side of the Text string which you want to extract.
  • For Example,  =LEFT(“application”,3) returns the 3 number of characters from left side i.e. “app”.

Points to Remember about LEFT Function

  • In the left & right function, Num_chars must be equal to OR greater than zero. Otherwise, it returns #Value error
  • If the num_chars argument in the left or right function is greater than the length of text, LEFT returns all of the text. For Example, =LEFT (“FUNCTION,” 25) returns “FUNCTION.”
  • If the num_chars argument in the left or right function is omitted, it will consider or assume 1, by default. For Example,  =LEFT(“Swift”) returns “S.”

#2 – MID Substring Function

It extracts a given specific number of characters from the middle part of a supplied text string.

The syntax or formula for the MID function in Excel is:

MID Formula

All the argument is compulsory & required parameters

The MID function syntax or formula has the below-mentioned arguments:

  • Text: It is the text string that contains the characters where you want to extract
  • start_num: specifies the position of the first character or starting position of the substring from where you want to begin
  • num_chars: It is the number of characters from the middle part of the text string you want to extract. (begins with start_num).
  • For Example,  =MID(“majori”,2,5) returns the substring from 2nd character and 5 letters or alphabet from 2nd character i.e. “ajori”.

Points to Remember

  • If start_num is greater than the length of the text, then the MID function returns an empty value
  • If start_num is less than 1, then the MID function returns #VALUE! error.
  • If num_chars is a negative value, then the MID function returns #VALUE! error.

#3 – RIGHT Substring Function

It extracts a given specific number of characters from the RIGHT side of a supplied text string.

The syntax or formula for the RIGHT function in Microsoft Excel is:

Right Function Formula

The RIGHT function syntax or formula has the below-mentioned arguments:

  • Text: (Compulsory or required parameter) It is the Text string that contains the characters where you want to extract
  • num_chars: (Optional parameter) It is the number of characters from the RIGHT side of the text string you want to extract.
  • For Example,  =RIGHT(“application”,6) returns the 6 number of characters from right side i.e. “cation”.

How to use the SUBSTRING Function in Excel?

Let’s look out how the SUBSTRING functions work in Excel.

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
You can download this SUBSTRING Function Excel Template here – SUBSTRING Function Excel Template

Example #1 – Extract a Substring in Excel Using LEFT Function

In the below-mentioned example, Cell “B3” contains employee id with the name. Here I need to extract only employee ID with the help of LEFT FUNCTION

Substring in Excel - Example1

Let’s apply the “LEFT” function in cell “C3”. Type =LEFT( in the cell “C3” where arguments for the LEFT function will appear. i.e. =LEFT (text, [num_chars]) it needs to be entered

Substring in Excel - Example1-1

Text: It is the text string that contains the characters where you want to extract substring excel, i.e., “B3” or “648 MANOJ.”

Substring in Excel - Example1-2

num_chars: It is the number of characters from the left side of the text string you want to extract. Here employee id contains 3 numbers, so I want to extract the first three numbers only

Substring in Excel - Example1-3

Click the ENTER key after entering all the LEFT function arguments. i.e. =LEFT(B3,3)

It extracts the first 3 characters from the text, i.e., 648

Substring in Excel - Example1-4

Example #2 – Extract a Substring in Excel Using RIGHT Function

In the below-mentioned example, it contains domain or website names. Here I need to extract the last three characters with the help of the RIGHT function

Example 2

Let’s apply the “RIGHT” function in cell “C3”. Type =RIGHT( in the cell “C3” where arguments for the RIGHT function will appear. i.e. =RIGHT (text, [num_chars]) it needs to be entered

Substring in Excel - Example 2-1

Text: It is the text string that contains the characters where you want to extract substring excel, i.e., “B3” or “GMAIL.COM.”

Substring in Excel - Example 2-2

num_chars: It is the number of characters from the right side of the text string you want to extract. Here all the website name ends with “COM,” so I want only the last three characters

Substring in Excel - Example 2-3

Click the enter key after entering all the RIGHT function arguments. i.e. =RIGHT(B3,3).

Right function

Similarly, it is applied to other cells, or else you can also you can use the drag&drop option to get the desired output

Substring in Excel - Example 2-5

It extracts the last 3 characters from the text, i.e., COM

Example #3 – Extract a Substring in Excel Using MID Function

In the below-mentioned example, Cell “B3” contains PHONE NUMBER with area code. Here I need to extract only area code with the help of MID FUNCTION

Example 3

Let’s apply the “MID” function in cell “C3”. Type =MID( in the cell “C3” where arguments for Mthe ID function will appear. i.e. =MID(text,start_num,num_chars) it needs to be entered

Substring in Excel - Example 3-1

Text: It is the text string that contains the characters where you want to extract substring excel, i.e., “G14” or “(248)-860-4282.”

Substring in Excel - Example 3-2

start_num: It specifies the position of the first character or starting position of the substring from where you want to begin, i.e., In the phone number, numbers in the bracket are the area code. I want only those numbers which are inside the bracket, i.e., 248.  Here number inside the bracket begins from 2nd position.

Substring in Excel - Example 3-3

num_chars: It is the number of characters from the middle part of the text string you want to extract. (it begins with start_num). I want only those 3 numbers where are area code present inside the bracket, i.e., 3 characters

Substring in Excel - Example 3-3

Click the enter key after entering all the MID function arguments. i.e. =MID(B3,2,3)

mid function

It extracts the 3 characters or numbers which are present inside the bracket, i.e., 248

Things to Remember

Substring in Excel Function has wide applications i.e.

  • It is used to obtain the domain name from an email address with the help of the right function
  • It is used to obtain the first, middle & last name from the full name with substring functions
  • It is used to remove the trailing slash in Web URLs
  • It is used to extract country or state code from the phone number

Recommended Articles

This has been a guide to SUBSTRING in Excel. Here we discuss how to use Substrings functions in Excel – LEFT, MID, and RIGHT along with excel examples and downloadable excel templates. You may also look at these useful excel tools –

  • String Functions in Excel VBA
  • ISNA Function
  • STRING to Date in VBA
  • VBA Substring
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 SUBSTRING Function Excel Template

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