How to use Substring Functions in Excel? (LEFT, MID & RIGHT)

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.

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Substring in Excel (wallstreetmojo.com)

3 Types of Substring Functions

LEFT Substring Function
RIGHT Substring Function
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:

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:

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 2^nd character and 5 letters or alphabet from 2^nd 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:

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

19th/20th September 2020	Saturday - Sunday	9 am IST to 5 pm IST
26th/27th September 2020	Saturday - Sunday	9 am IST to 5 pm IST

Substring in Excel