The **MID function** extracts a specified number of characters from a cell with a customizable starting point. In this article, we will demonstrate 5 practical examples of how to use it in Excel.

**Excel MID Function (Quick View)**

## Introduction to the MID Function

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

`=MID(text,start_num,num_chars)`

**Arguments:**

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 regardless of 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**to apply it unless you’re using Office365 version.

**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 the ** start_num**,

*greater than the total length of the text*

**7**, is**“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 characters on the left of 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,**

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

**“ple”**.

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

We used the Microsoft Excel 365 version for this article; however the methods should work in any version after Excel 2003.

### Method 1 – Using MID Function to Extract First Name

Suppose we have the list of names as shown in the following image. We’ll extract the **First Name **by using the **MID **function.

**Steps:**

- Use the following formula in cell
**C5**:

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

Cell **B5 **represents the first cell of the **Name **column.

- Press
**ENTER**.

The **MID **function will return the **First Name**.

- Drag the
**Fill Handle**down to get the remaining outputs.

### Method 2 – Using MID function to Extract the Middle Name

Now we will use the **MID **function to extract the **Middle Name **from the list of names.

**Steps:**

- Enter the formula given below in cell
**C5**:

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

**Formula Breakdown **

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

**MID(B5,FIND(” “,B5)+1,FIND(” “,B5,FIND(” “,B5)+1)-FIND(” “,B5)-1)**→ becomes**MID(B5,6+1,FIND(” “,B5,6+1)-6-1)**.**FIND(” “,B5,6+1)**returns**12**as output.**MID(B5,6+1,FIND(” “,B5,6+1)-6-1)**→ becomes**MID(B5,6+1,12-6-1)**.**MID(B5,6+1,12-6-1)**returns the**Middle Name**.**Output**→**James**.

- Press
**ENTER**.

- The
**Middle Name**from cell**B4**is returned.

- Use the
**AutoFill**option to get the rest of the outputs.

### Method 3 – Using MID Function to Extract the Last Name

Now we will extract the **Last Name**.

Consider the data set below:

In this dataset, we have the **Employee IDs**, **Employee Names**, and **Email IDs **of some employees.

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

- The formula in cell
**E5**will be:

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

Cell **C5 **indicates the first cell of the **Employee Name** column.

**Formula Breakdown**

**SEARCH(” “,C5)**returns the position of a space (**“ ”**) in the text within cell**C5**. See the**SEARCH function**for details.**Output**→**8**.

- 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 the character after the space, not from the space itself. - We set the
argument of the*num_chars***MID**function to**LEN(C5)-SEARCH(” “,C5)**. **LEN(C5)**provides the total length of the name, and**SEARCH(” “,C5)**finds the space within the name. See the**LEN function**for details.- So,
**LEN(C5)-SEARCH(” “,C5)**returns how many characters remain after the space, which is the length of the last name.**Output**→**14-8**.

**MID(C5,SEARCH(” “,C5)+1,LEN(C5)-SEARCH(” “,C5))**→ becomes**MID(C5,8+1,14-8)**.- The
**MID**function extracts all the characters of the name within the cell**C5**after the space, which is the last name.**Output**→**Austin**.

- Drag the
**Fill Handle**down over the rest of the cells.

The last names of all the employees are returned.

Using this process, what would the formula be to extract whether the emails are from **gmail.com** or **yahoo.com**?

- Just replace the space (
**“ ”**) in 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.*

### Method 4 – Using 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 to start to extract the last two digits.**Output**→**2**.

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

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

The last **two **digits of all the **Employee IDs **are extracted.

### Method 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 we can use all the strings within the **MID **function simultaneously.

Let’s 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))`

The range **C5:C20 **refers to the cells in 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, press Ctrl + Shift + Enter to enter it unless using Office365.*

The **Last Names **of all the employees are returned simultaneously.

## Common Errors with MID Function

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 |

**Download Practice Workbook**

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