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.

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:

The LEFT function syntax or formula has 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 left & right function, Num_chars must be equal to OR greater than zero. Otherwise, it returns #Value error
• If the num_chars argument in left or right function is greater than the length of text, LEFT returns all of text. For Example, =LEFT (“FUNCTION”, 25) returns “FUNCTION”
• If the num_chars argument in 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 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 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.

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 “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 ENTER key, after entering the 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 last three characters

Click 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 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 “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 is 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 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 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