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:
=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
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))
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
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 positionLEN(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))
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.