How to Use MID Function in Excel (3 Examples)

Quick View of the MID Function

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)

Quick View of the MID Function


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

Syntax of the MID Function

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.

MID Function with start_num Greater than the Length of the String

  • 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”.

MID function with num_chars more than the left characters


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.

Data Set in Excel

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.

Extracting the Last Name from a Name Using the MID Function

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

Extracting the Email Extension Using the MID Function

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.

Extracting Last Two Digits of a Number Using the MID Function

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
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))
[This is an Array Formula. So do not forget to press Ctrl + Shift + Enter to enter this.]

Array Formula with MID Function

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

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

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo