EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources TEXT and String Functions in Excel Substring Excel Function
Secondary Sidebar
Excel Functions
  • TEXT and String Functions in Excel
    • Excel CODE Function
    • Excel Find
    • TRIM Formula in Excel
    • Excel CHAR Function
    • LEFT Formula in Excel
    • Opposite of Concatenate in Excel
    • Concatenate Strings in Excel
    • Extract Numbers From String
    • VALUE Formula in Excel
    • Left TRIM in Excel
    • Substring Excel Function
    • SUBSTITUTE Function in Excel
    • Excel VALUE Function
    • Excel REPT Function
    • Excel TRIM Function
    • Excel CLEAN Function
    • Excel EXACT Function
    • PROPER Function in Excel
    • Excel LEFT Function
    • MID Function in Excel
    • LEN Function in Excel
    • CONCATENATE Function in Excel
    • SEARCH Function in Excel
    • RIGHT Function in Excel
    • FIND Function in Excel
    • TEXT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Online COURSE
  • Online Excel Data Analysis Training

Substring Excel Function

By Madhuri ThakurMadhuri Thakur

Substring Function in Excel

Excel Substring Function (Table of Contents)

  • Substring Function in Excel
  • How to Use Substring Function in Excel?
    • #1 – LEFT Substring Function in Excel
    • #2– RIGHT Substring Function in Excel
    • #3– MID Substring Function in Excel

Substring Function in Excel

Substring is the way to extract some of the text from the cell in Excel. In Excel, we do not have any Substring function, but we can use LEN, Left, Right, Mid, Find function to slice the value there in a cell. For using Substring, we need to start the function with Left or Right and then select the cells from where we need to get the text, use the LEN function to get the length of characters we want to extract, then subtract the remaining characters using FIND function. By this, we will get Substring.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

The LEFT, RIGHT, and MID functions are categorized under the TEXT function, which is inbuilt.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,412 ratings)

It helps us to:

  • To get a string of any text.
  • To find out a specifically required text of any string.
  • To find out the position of any text in a string.

We can find these functions under the Formulas Tab.

Below is the screenshot for navigation purpose:

Substring Function

So, the above three are the most commonly used functions to find out the string for text.

How to Use Substring Function in Excel?

The substring function in Excel is very simple and easy to use. Let’s understand the working of the Substring function in Excel by some examples.

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

#1 – LEFT Function in Excel

LEFT Function helps the user to know the substring from the left of any text. It starts counting with the leftmost character.

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: This argument is compulsory as it represents the text from where we want to extract the characters or cell address.
  • Num_chars: This one is an optional argument representing the number of characters from the left side of text or character, which we want to extract.

Extract a Substring in Excel Using LEFT Function

Suppose we want the first two characters or substring from the given example in below screenshot:

Left Function 1

In the cell C4, a text is given from which we want the first two characters. We have to type =LEFT and then use ( or press Tab in D4, look at the screenshot for reference:

Left Function 2

Now, we can see the argument required in the screenshot as well. We have to type or navigate the cell address to C4, and in place of num_chars, we need to put the number of character we want, so in this case, two characters are needed, so we put 2.

Below is the screenshot for the above step:

Left Function 3

After putting an argument, we need to press ) or just press Enter to get the result. Below is the screenshot after completing the formula.

Left Function 4

So, the result is Mr which is two characters of MrRam from the left. To extract the substring from the left, we can use the LEFT function in excel.

Points to Remember while using LEFT Function in Excel

  • The result or return value depends on the arguments which are inserted in the syntax.
  • If the argument is less than (num_chars) 0, then the result would be a #VALUE error.
  • The output or return value can be string or text.
  • A LEFT function in excel is used for extracting the substring from the string, which calculates from the leftmost character.

#2 – RIGHT Function in Excel

RIGHT function from Text also helps to get the substring from the rightmost of a text. If we do not put a number, then it will show one character of the rightmost.

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

RIGHT Formula

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

  • Text: It is the text string that contains the characters where you want to extract them.
  • num_chars: It is the number of characters from the RIGHT side of the text string you want to extract.

Extract a Substring in Excel Using RIGHT Function

Suppose we have to extract the last two characters of a word as shown in the below example:

Substring Right Function 1

Suppose we have to find out the last two character of the D4 cell, then type =RIGHT in E4. Below is the screenshot for reference:

Substring Right Function 2

After that, we have to click on Tab or press (

Substring Right Function 3

We can see that the required argument is populating in the above screenshot. Accordingly, on the place of text, put cell address which is D4 here, and we want two 2 characters from right to put 2. Below is the screenshot for reference:

Substring Right Function 4

After putting all the argument close, the formula by pressing ) or just hit Enter to know the result.

Substring Right Function 5

Hence, we can see in the above screenshot that 11 is populated after hitting Enter; the formula captures the last two characters from the right as entered in the argument. RIGHT Function helps to get the string from the right side of any character.

Points to Remember while using RIGHT Function in Excel

  • The output depends on the syntax.
  • The output will be a #VALUE error if the parameter is less than (num_chars) 0.
  • The result can be a string as well as text.

#3 – MID Function in Excel

It helps us to extract a given number of characters from the middle of any string of text.

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

MID Formula

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

  • Text: It is the original text string or character from which we have to extract the substring or characters.
  • Start_num: It indicates the position of the first character where we want to begin.
  • num_chars: It is the number of characters from a middle part of the text string.

Like the LEFT and RIGHT function, we can also use the MID function to get the substring of text in Excel.

Extract a Substring in Excel Using MID Function

Suppose, in the below example, we want to extract the three characters from the middle of the text, then we can use MID Function.

Substring Mid Function 1

We will see how to use MID Function to extract the string in the middle in cell E5, step by step.

We have to type =MID and then press Tab or press ( like shown in the below screenshot.

Substring Mid Function 2

Now, we have to enter the arguments as shown in the above pic.

In place of text, we have to enter the cell address, which is D5 here, start_num is the argument which defines from where we want the string, so in this case, we want it from 6th from left, and the last argument is num_chars, which defines about the number of character we want, so here it is 3.

Substring Mid Function 3

So, in the screenshot, we can see that argument is put down as per requirement. After putting an argument, close the formula with “)”, or just hit Enter to get the results. Below is the screenshot for reference.

Substring Mid Function 4

So, in the above screenshot, the TV7 is the result of the MID Function applied. TV7 is the three characters from the 6th position of the text as an example.

Things to Remember about Substring Function in Excel

  • The substring function is used to get the phone number or code as per requirement.
  • It is also used to get the domain name of the email id.
  • While using a substring in excel, the text function needs to be conceptually clear to get the desired results.
  • An argument should be put down in the formula as per the requirement.

Recommended Articles

This has been a guide to Substring Function in Excel. Here we discuss how to use Substrings functions in Excel – LEFT, RIGHT, and MID Function along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. RIGHT Formula in Excel
  2. RIGHT Excel Function
  3. Excel Text with Formula
  4. VBA SubString
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Data Analysis Training (17 Courses, 8+ Projects)4.9
2 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Substring Function Excel Template

EDUCBA

Download Substring Function Excel Template

EDUCBA

डाउनलोड Substring Function Excel Template

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more