Excel MID Function

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.


Excel MID Function Syntax

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)
  • text - The original string of text, either as a cell reference or as a string enclosed in quotes
  • start - The number of the starting character of the desired text (the first character is 1, the second is 2, and so on)
  • length - The number of characters to extract from the original string

Examples

=MID("Some text", 3, 5)

The function above returns the string "me te". See explanation below:

=MID("Some text")
------123456789
--------12345

We 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...
---1234567


More Excel MID Function Examples

The 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".

Jennifer
12345678
-12345

=MID(D1, 2, 2)

This MID function would return "AL".

SALES
12345
-12

When Will the MID Function return a Blank String?

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.



Using MID With Other Excel Functions

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


Combining LEN and MID Functions in Excel

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

Continue to MID practice exercises!