The MID function extracts a given number of characters from the middle of a supplied text string. MID takes three arguments, all of which are required. The first argument, text, is the text string to start with. The second argument, start_num, is the position of the first character to extract. The third argument, num_chars, is the number of characters to extract. If num_chars is greater than the number of characters available, MID returns all remaining characters.
MID function basics
To extract text with MID, just provide the text, the starting position, and the number of characters to extract. The formulas below show how to extract one, two, and three characters with MID:
=MID("apple",1,1) // returns "a"=MID("apple",1,2) // returns "ap"=MID("apple",1,3) // returns "app"The formula below returns 3 characters starting at the 5th character:
=MID("The cat in the hat",5,3) // returns "cat"This formula will extract 3 characters starting at character 16:
=MID("The cat in the hat",16,3) // returns "hat"If num_chars is greater than the remaining characters, MID will all remaining characters:
=MID("apple",1,100) // returns "apple"This can be useful as a way to simply certain formulas as explained below. MID can extract text from numbers, but the result is text:
=MID(12348,3,4) // returns "348" as textExample 1 - extract date from serial number
In the example below, we use the MID function to extract a date in YYYYMM notation from a serial number with 14 characters. The formula in cell D5, copied down, is:
=MID(B5,5,6)
Note that the date is returned as text like "202105" which indicates the year and month but is not a valid date. If you want a valid date, you could use a formula like this:
=DATE(MID(B5,5,4),MID(B5,9,2),1)Here we use the MID function twice: once to get the year, and once to get the month. The day is hard-coded as 1, and all values are returned to the DATE function, which returns a valid Excel date like 1-May-2021.
Example 2 - extract name from email address
A common challenge with MID is how to provide a variable end point. This can be done by combining MID with the FIND function. For example, in the worksheet below, the goal is to return the name portion of each email address. To perform this task, MID should start at the first character and end at the character before the "@" symbol. The formula in cell D5 looks like this:
=MID(B5,1,FIND("@",B5)-1)
As the formula is copied down, the FIND function returns the position of the "@" symbol as a number. We then subtract 1 and the result goes into the MID function as the num_chars argument.
Example 3 - extract domain from email address
A relatedchallenge with MID is how to provide a variable starting point. This too can be accomplished by combining MID with the FIND function. In the worksheet below, the goal is to return the domain portion of each email address. To perform this task, MID should start at the character after the "@" symbol and continue to the end of the text string. The formula in cell D5 looks like this:
=MID(B5,FIND("@",B5)+1,100)
In this formula, the starting position (start_num) is provided by FIND, which returns the location as a number of the "@" symbol. We then add 1 to the result from FIND to start at the next character. For num_chars, we hardcode 100. This is a simple hack. When num_chars exceeds the characters that remain in a text string, MID returns all remaining characters. We take advantage of this behavior by providing an arbitrarily large number that will work in all cases.
Example 4 - MID with IF
You can easily combine the MID function with the IF function to create "if cell contains" logic. In the example below, a formula is used to flag serial numbers that contain "2021". The formula in cell D5 is:
=IF(MID(B5,5,4)="2021","x","")
The MID function is configured to extract 4 characters beginning at character 5. The result is then compared to "2021" as the logical test inside the IF function. When the result is TRUE, IF returns "x". Otherwise, IF returns an empty string (""). The result is that serial numbers that contain "2021" are clearly identified.
Example 5 - MID with SEQUENCE
The MID function can also be used together with the SEQUENCE function to split a text string into an array of characters. This pattern shows up in more advanced formulas where it is necessary to iterate through a text string one character at a time. The generic version of the formula looks like this:
=MID(A1,SEQUENCE(1,LEN(A1)),1)In brief, SEQUENCE spins up a numeric array based on the length of the text string in A1 and this array is delivered to the MID function as the start_num argument, with num_chars hardcoded as 1. The results is an array that contains all characters in the text string. See this page for a full explanation.
Related functions
Use theMID function to extract from the middle of a text string. Use theLEFT function to extract text from the left side of a text string andthe RIGHT functionto extract text starting from the right side of a text string. The LEN function returns the length of text as a count of characters.Use FIND or SEARCH to locate an unknown starting or ending position.
In the latest version of Excel, newer functions like TEXTBEFORE, TEXTAFTER, and TEXTSPLITgreatly simplify certain text operations and make some traditional formulas obsolete. Example here.
Notes
- All three arguments are required.
- The output from MID is always text.
- MID can extractnumbers as well, but the result is text.
- MID ignoresnumber formatting when extracting characters.
- When num_chars is greaterthan the remaining text, MID returns all remaining characters
