Excel has several functions to manipulate text values. The MID function returns a string of text from within a larger string of text. A "string" of text in Excel is just any text inside a cell or enclosed in quotes in a function. While the LEFT and RIGHT functions return the leftmost and rightmost characters from a string of text, the MID function returns text from within the middle of the string. The MID function can help you strip away unnecessary characters from a string of text so that only the important part remains.
The MID function in Excel returns text of a certain length from the inside of a string (neither the leftmost nor the rightmost). The MID function takes three arguments:
=MID(text, start, length)=MID("Some text", 3, 5)The function above returns the string "me te". See explanation below:
=MID("Some text")------123456789--------12345We start counting at character 3 of the original string, and take five characters (including the space) so that the function returns "me te".
Suppose cell A1 contained the text "Excel Exercises". The function =MID(A1, 3, 7) would return "cel Exe" - see below.
"Excel Exercises"-123456789...---1234567The MID function in Excel works with a text argument in quotes, or with a cell reference. Below are some examples of the MID function with a cell reference argument.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | NAME | ID | AGE | SALES |
| 2 | Bob | 1234 | 32 | 5,000 |
| 3 | Jennifer | 5678 | 45 | 7,250 |
=MID(A3, 2, 5)This MID formula would return "ennif".
Jennifer12345678-12345
=MID(D1, 2, 2)This MID function would return "AL".
SALES12345-12
The MID Function in Excel will return a blank string if the second argument (start number) is larger than the length of the original string. For example, =MID("hello", 6, 2) would return a blank string because "hello" doesn't have a 6th character.
=MID(A2, 4, 2) from the table above would also return a blank because the text in cell A2 doesn't have a 4th character.
As with any Excel function which takes numerical inputs as arguments, MID can accept other functions which return numbers.
Take the example below where we use the LEN function as inputs to the MID function:
=MID("Excel Exercises", LEN("Hello"), LEN("cell"))
Hello
12345
cell
1234
The first LEN function returns 5, the second returns 4, so the MID function equates to =MID("Excel Exercises", 5, 4), which would return "l Ex".
"Excel Exercises"-123456789...-----1234The MID function can be combined with other functions in Excel, such as LEN, to help you separate unwanted characters from strings.
The table in the last example is nicely separated into separate columns for the employee's name, ID, age, and sales. Now suppose your boss sends you an Excel file with a single string for each employee, containing their ID, name, and age, all in the same cell:
| A | |
|---|---|
| 1 | 1234 Bob 32 |
| 2 | 5678 Jennifer 45 |
You know that ID is always 4 characters and age is always 2 digits, but name can be any length. =MID(A1, 6, 3) will work to return "Bob", but the same function applied to A2 will only return "Jen" for Jennifer.
How do you write a reusable function to extract each employee's first name?
You know the length of the whole string in A1 is =LEN(A1). You also know that the name always begins at the 6th position of the string (because of the 4 digit ID + the space), and that there are 8 characters in each string that you DON'T want (ID, age, and two spaces).
Therefore, you can write the following function to return "Bob" when applied to cell A1, and return "Jennifer" when applied to cell A2:
=MID(A1, 6, (LEN(A1)-8))