Excel Functions Tutorials

- TEXT Functions in Excel
- Char Function In Excel
- CLEAN Excel Function
- CODE Excel Function
- Concatenate Excel Function
- CONCATENATE Formula in Excel
- Concatenate Date in Excel
- Concatenate Excel Columns
- Concatenate Strings in Excel
- Opposite of Concatenate in Excel
- EXACT Excel Function
- FIND Function in Excel
- LEFT Function in Excel
- LEN in Excel
- MID in Excel
- MID Formula in Excel
- PROPER in Excel
- Proper Formula in Excel
- REPT Function in Excel
- RIGHT Function in Excel
- Right Formula in Excel
- Search Function in Excel
- Substitute Function in Excel
- Substring in Excel
- TEXT Function in Excel
- TEXT Formula in Excel
- Trim in Excel
- Trim Formula in Excel
- VALUE Function in Excel
- Value Formula in Excel

- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)
- VBA (162+)

Related Courses

**Table of Contents**

- SUBSTRING Function in Excel
- Definitions of Substring Function
- How to use SUBSTRING Function in Excel?

## SUBSTRING Function in Excel

The three Excel text substring functions are **LEFT**, **RIGHT** and **MID. **It’s a pre-built integrated function in excel that is categorized under TEXT function.

It is the most commonly used text function to perform the below-mentioned task

- To extract specifically required text from a cell
- To get a substring after or before a specified character
- To find out cells containing part of a string

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 Microsoft 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 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

4.9 (1,353 ratings)

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 “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 the 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 About Substring Function in Excel**

Substring in Excel Function has wide applications i.e.

- Substring in excel is used to obtain the domain name from an email address with the help of right function
- Substring in excel is used to obtain the first, middle & last name from the full name with substring functions
- Substring in excel is used to remove the trailing slash in Web URLs
- Substring in excel 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
- MID Formula in Excel
- VBA Left Function
- How to Format Phone Numbers in Excel?
- Excel VBA MID Function
- Add Text in Formula Excel
- Automatic Numbering
- Number in Excel
- Char In Excel
- REPLACE in Excel
- TEXT in Excel
- ISNA Excel Function

- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion