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.
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 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:
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.

4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
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
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 –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion