Today I will be showing how you can extract some specific number of characters starting from a specific position of a string using the **MID** function of Excel.

MID Function of Excel (Quick View)

**Table of Contents**hide

**Download Practice Workbook**

**Excel MID Function: Syntax and Arguments**

**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 or Optional |
Value |

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

start_num | Required | The starting position from which characters will be extracted. 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 any 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
**start_num**argument is greater than the total number of characters of a string, the**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
**start_num**position is less than the argument**num_chars,**the**MID**function will return all the left characters starting from the**start_num**position.

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

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

**“ple”**.

**Excel MID Function: 3 Examples**

### 1. Using MID Function with Texts: Extracting the Last Names from Some Names

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

Look at the data set below.

We have the Employee IDs, Employee names, and the 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(C4,SEARCH(" ",C4)+1,LEN(C4)-SEARCH(" ",C4))`

Enter this formula into the first cell and then drag the **Fill Handle** through the rest of the cells.

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

**Explanation of the Formula**

tells where there is a space (“ ”) in the name within cell`SEARCH(" ",C4)`

**C4**. See the**SEARCH**function for details.

But we set the **start_num** argument of the **MID** function to ** SEARCH(" ",C4)+1, **because we want to extract the last name which starts from just after the space, not from the space.

- We set the
**num_chars**argument of the**MID**function to`LEN(C4)-SEARCH(" ",C4)`

.

** LEN(C4)** tells the total length of the name, and

**tells where there is a space within the name. See the**

`SEARCH(" ",C4) `

**LEN**function for details.

So ** LEN(C4)-SEARCH(" ",C4) **tells how many characters remain after the space, this is the length of the last name.

- So,
extracts all the characters of the name within the cell`MID(C4,SEARCH(" ",C4)+1,LEN(C4)-SEARCH(" ",C4))`

**C4**, after the space. That is the last name we are searching for.

Formula |
Output |
Explanation |

MID(C4,SEARCH(” “,C4)+1,LEN(C4)-SEARCH(” “,C4)) | Austin | Return all the characters after the space (“ ”) of the name within cell C4. That is the last name. |

Now if 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?

Easy. Just replace the space (“ ”) of the previous formula with a “@”.

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

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

**2. Using MID Function with Numbers: Extracting 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(B4,LEN(B4)-2+1,2)`

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

See, we have extracted the last two digits of all the IDs.

**Explanation of the Formula**

tells us from where we have to start if we want to extract the last two digits.`LEN(B4)-2+1`

extracts two digits starting from the position`MID(B4,LEN(B4)-2+1,2)`

`LEN(B4)-2+1`

.

Formula |
Output |
Explanation |

MID(B4,LEN(B4)-2+1,2) | 01 | Extracts two digits starting from the position LEN(B4)-2+1. These are the last two digits. |

**Note: **Though we have extracted two digits from a number, they have been extracted as text values. See the extracted values are aligned left within the cells by default.

If they had been numbers, they would have been aligned right.

**3. 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(C4:C19,SEARCH(" ",C4:C19)+1,LEN(C4:C19)-SEARCH(" ",C4:C19))`

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

**Explanation of the Formula**

The Array Formula ** MID(C4:C19,SEARCH(" ",C4:C19)+1,LEN(C4:C19)-SEARCH(" ",C4:C19)) **consists of 16 single formulas.

`MID(C4,SEARCH(" ",C4)+1,LEN(C4)-SEARCH(" ",C4))`

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

`MID(C6,SEARCH(" ",C6)+1,LEN(C6)-SEARCH(" ",C6))`

`…`

`…`

`…`

`MID(C19,SEARCH(" ",C19)+1,LEN(C19)-SEARCH(" ",C19))`

Each of them extracts the last name (See Example 1 for details)

Formula |
Output |
Explanation |

MID(C4:C19,SEARCH(” “,C4:C19)+1,LEN(C4:C19)-SEARCH(” “,C4:C19)) | Austin
Johnson Smith … … … Marlo |
Return all the characters after the space (“ ”) of all the names within the cell range C4 to C19. These are the last names we are looking for. |

**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 start_num or the num_chars argument is a text value or a negative number. |

**Conclusion**

So, using these methods, you can extract any number of characters starting from a specific position of any given string. Do you have any questions? Feel free to ask us.