How to Use MID Function in Excel (3 Examples)

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)

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:

Argument

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:

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

• SEARCH(" ",C4)tells where there is a space (“ ”) in the name within cell 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 SEARCH(" ",C4) tells where there is a space within the name. See the 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, MID(C4,SEARCH(" ",C4)+1,LEN(C4)-SEARCH(" ",C4)) extracts all the characters of the name within the cell C4, after the space. That is the last name we are searching for.

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 “@”.

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:

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

• LEN(B4)-2+1 tells us from where we have to start if we want to extract the last two digits.
• MID(B4,LEN(B4)-2+1,2) extracts two digits starting from the position LEN(B4)-2+1. 

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:

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.

1. MID(C4,SEARCH(" ",C4)+1,LEN(C4)-SEARCH(" ",C4))
2. MID(C5,SEARCH(" ",C5)+1,LEN(C5)-SEARCH(" ",C5))
3. MID(C6,SEARCH(" ",C6)+1,LEN(C6)-SEARCH(" ",C6))

…

…

…

16. MID(C19,SEARCH(" ",C19)+1,LEN(C19)-SEARCH(" ",C19))

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

Common Errors with MID Function

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.

Further Readings

• How to use SUBSTITUTE function in Excel (4 Examples)
• Use UPPER Function in Excel (4 Examples)
• How to Use SEARCH Function in Excel (3 Examples)
• use PROPER function in Excel (2 Examples)
• How to Use LEN Function in Excel (Formula and VBA Code)