While working in Excel, we often need to extract a specific number of characters from a cell. This can be achieved by both the LEFT and the RIGHT functions. But using the **LEFT **and the **RIGHT **functions fixes the starting point of the extraction process. By using the **MID** function, we can counter this problem. Basically, the MID function allows us to extract a specified number of characters from a cell with a customizable starting point. In this article, we will learn 5 practical examples to use the MID function in Excel. So, let’s start the article and explore these examples.

**Excel MID Function (Quick View)**

The overview of the **MID **function is demonstrated in the following image. To be clear, we will discover more about the applications of the **MID **function in the following sections.

## Introduction to Excel MID Function

The **Syntax **and **Arguments **along with the **Return Value **of the M**I**D function are described in the following section.

**Summary:**

- Returns a specific number of characters from the middle of a string, given a specific starting position.
- Works for both arrays and non-arrays.
- Available from
*Excel 2003*.

**Syntax:**

The syntax of the **MID **functions is:

`=MID(text,start_num,num_chars)`

**Argument:**

Argument | Required/Optional | Explanation |
---|---|---|

text |
Required | The string from which characters will be extracted. It can be any text value, number, or array. |

start_num |
Required | The starting position from which characters will be extracted. It can be a single number or an array of numbers. |

num_chars |
Required | The total number of characters that will be extracted. Can be a single number or an array of numbers. |

*Note:*

*The first argument***text**can be any text value, number, or array of text values or numbers. But whether it is a text value or a number, the return value will always be a text value.*The next two arguments***start_num**and**num_chars**can be any number or an array of numbers.*If you use an array argument, the formula will be an***Array Formula**and you have to press**Ctrl + Shift + Enter**.

**Return Value:**

Returns a text value consisting of a specific number of characters starting from a specific position of a string.

**Special Notes:**

- If the
argument is greater than the total number of characters of a string, the*start_num***MID**function will return an empty string.

For example, the formula **MID(“Apple”,7,3) **will return an empty string because here the *start_num***7 **is greater than the total length of the text **“Apple”, 5**.

- If the total number of characters of the string starting from the
position is less than the argument*start_num*the*num_chars*,**MID**function will return all the left characters starting from theposition.*start_num*

For example, in the formula **MID(“Apple”,3,7), **the total number of characters starting from the ** start_num** position 3 is 3 (

**“ple”**), which is less than the

**argument 7. So it will return only these 3 characters,**

*num_chars***“ple”**.

## How to Use the MID Function in Excel: 5 Examples

In this section of the article, we will discuss five appropriate examples of using the **MID **function in Excel. Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.

### 1. Using MID Function to Extract First Name

In the first example, we will learn to use the **MID **function to extract the **First Name **from a list of names. Let’s say, we have the list of names as shown in the following image. Our goal is to extract the **First Name **by using the **MID **function. Let’s follow the steps mentioned below to do this.

**Steps:**

- Firstly, use the following formula in cell
**C5**.

`=MID(B5,1,FIND(" ",B5))`

Here, cell **B5 **represents the first cell of the **Name **column.

- After that, press
**ENTER**.

As a result, the **MID **function will return the **First Name **as shown in the following image.

- Finally, drag the
**Fill Handle**to get the remaining outputs.

### 2. Utilizing MID function to Extract the Middle Name

At this point, we will use the **MID **function to extract the **Middle Name **from the list of names. Let’s use the instructions outlined below to do this.

**Steps:**

- Firstly, apply the formula given below in cell
**C5**.

`=MID(B5,FIND(" ",B5)+1,FIND(" ",B5,FIND(" ",B5)+1)-FIND(" ",B5)-1)`

**Formula Breakdown **

- Here, the
**FIND function**returns the location of a specific value (in this case position of space) from a string.- Here,
**” “**→ It is theargument.*find_text* **B5**→ This indicates theargument.*within_text***Output**→**6**.

- Here,
- Now,
**MID(B5,FIND(” “,B5)+1,FIND(” “,B5,FIND(” “,B5)+1)-FIND(” “,B5)-1)**→ It becomes**MID(B5,6+1,FIND(” “,B5,6+1)-6-1)**. - After that,
**FIND(” “,B5,6+1)**returns**12**as output. - Then,
**MID(B5,6+1,FIND(” “,B5,6+1)-6-1)**→ It becomes**MID(B5,6+1,12-6-1)**. - Finally,
**MID(B5,6+1,12-6-1)**returns the**Middle Name**.**Output**→**James**.

- Following that hit
**ENTER**.

- Consequently, you will have the
**Middle Name**of the first cell from the list of names as demonstrated in the following picture.

- Finally, use the
**AutoFill**option of Excel to get the rest of the outputs.

### 3. Using MID Function to Extract the Last Name

Now we will extract the **Last Name** from some text values using the **MID **function of Excel.

Look at the data set below.

In the above dataset, we have the **Employee IDs**, **Employee Names**, and **Email IDs **of some employees of a company named **Mars Group**.

Let’s try to extract the **Last Names** of all the employees.

- The formula will be:

`=MID(C5,SEARCH(" ",C5)+1,LEN(C5)-SEARCH(" ",C5))`

Here, cell **C5 **indicates the first cell of the **Employee Name** column.

**Formula Breakdown**

**SEARCH(” “,C5)**tells where there is a space (**“ ”**) in the name within cell**C5**. See the**SEARCH function**for details.**Output**→**8**.

- Here, we set the
argument of the*start_num***MID**function to**SEARCH(” “,C5)+1,**because we want to extract the last name which starts from just after the space, not from the space.

- We set the
argument of the*num_chars***MID**function to**LEN(C5)-SEARCH(” “,C5).** - Here,
**LEN(C5)**tells the total length of the name, and**SEARCH(” “,C5)**tells where there is a space within the name. See the**LEN function**for details. - So,
**LEN(C5)-SEARCH(” “,C5)**tells how many characters remain after the space, this is the length of the last name.**Output**→**14-8**.

- So,
**MID(C5,SEARCH(” “,C5)+1,LEN(C5)-SEARCH(” “,C5))**→ It becomes**MID(C5,8+1,14-8)** - Finally, the
**MID**function extracts all the characters of the name within the cell**C5**, after the space. That is the last name we are searching for.**Output**→**Austin**.

- After that, drag the
**Fill Handle**through the rest of the cells.

You see, we have got the last names of all the employees beautifully.

Now that you understand this, can you tell me the formula to extract out for all the employees whether they use **gmail.com** or **yahoo.com** for the Emails?

- Simple! Just replace the space (
**“ ”**) of the previous formula with a**“@”**.

`=MID(D5,SEARCH("@",D5)+1,LEN(D5)-SEARCH("@",D5))`

*Note:**These types of problems can also be solved using the RIGHT function of Excel.*

### 4. Applying MID Function to Extract Last n Digits from Some Numbers

This time we will perform a comparatively easier task. We will extract the last **two **digits from the **Employee IDs** of all the employees.

- The formula will be:

`=MID(B5,LEN(B5)-2+1,2)`

**Formula Breakdown**

**LEN(B5)-2+1**tells us from where we have to start if we want to extract the last two digits.**Output**→**2**.

**MID(B5,LEN(B5)-2+1,2)**→ It becomes**MID(B5,2,2)**- Finally, the
**MID**function extracts two digits starting from the position**LEN(B5)-2+1.****Output**→**01**.

- Enter this formula in the first cell and then double-click on the
**Fill Handle**.

Have a look! We have extracted the last **two **digits of all the **Employee IDs**.

### 5. Using MID Function with Arrays

Up to now, we have used one string at a time and then dragged the **Fill Handle **for the rest of the strings.

But you can use all the strings within the **MID **function simultaneously.

Let’s try to extract the **Last Names** of all the employees simultaneously.

Instead of using one name at a time, we will use an array of all the names together.

- The formula will be:

`=MID(C5:C20,SEARCH(" ",C5:C20)+1,LEN(C5:C20)-SEARCH(" ",C5:C20))`

Here, the range of cells **C5:C20 **refers to the cells of the **Employee Name **column.

**Formula Breakdown**

- The Array Formula
**MID(C5:C20,SEARCH(” “,C5:C20)+1,LEN(C5:C20)-SEARCH(” “,C5:C20))**consists of**16**single formulas.**MID(C5,SEARCH(” “,C5)+1,LEN(C5)-SEARCH(” “,C5))****MID(C6,SEARCH(” “,C6)+1,LEN(C6)-SEARCH(” “,C6))****MID(C7,SEARCH(” “,C7)+1,LEN(C7)-SEARCH(” “,C7))****…****…****…****MID(C20,SEARCH(” “,C20)+1,LEN(C20)-SEARCH(” “,C20))**

- Each of them extracts the
**Last Name**(**See Example 3 for a detailed Formula Breakdown**)

*Note:** This is an Array Formula. So do not forget to press Ctrl + Shift + Enter to enter this.*

See, we have got the **Last Names **of all the employees simultaneously.

## Common Errors with MID Function

In this section of the article, we will discuss some of the common errors regarding the **MID **function and their possible reason for occurrences.

Error | When They Show |
---|---|

#VALUE! |
This shows when an argument is of the wrong data type. For example, when the or the start_num argument is a text value or a negative number.num_chars |

## Practice Section

In the **Excel Workbook**, we have provided a **Practice Section **on the right side of the worksheet. Please practice it yourself.

**Download Practice Workbook**

## Conclusion

That’s all for today. We have tried showing you how you can use the **MID **function in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any of your **MID **function-related scenarios where you have stuck, we are ready to help. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy.

**<< Go Back to Excel Functions ****|**** Learn Excel**