MID Function

The MID function enables you to extract a portion of a text string based on a starting position you specify and the number of characters you want to extract. The LEFT and RIGHT functions enable you to extract text from the left or the right edge of the string while MID enables you to extract text from the middle of the string. The MID function is often used with the LEN function which returns the length of a text string (that is the number of characters in the string).

Syntax

MID(text, start_num, num_chars)

Arguments

Argument	Description
Text	Required. A text string or a cell reference containing the characters you want to extract.
Start_num	Required. This is a number representing the starting position of the first character you want to extract in text . The first character in text starts with 1, the second is 2 and so on.
Num_chars	Required. This is a number that specifies the number of characters you want to extract from text .

Notes:

• If the start_num argument is larger than the length of the string in our text argument, MID will return an empty text (“”).
• MID will return the #VALUE! error if start_num is less than 1.
• MID returns the #VALUE! error if num_chars is a negative value.

Example

The examples below demonstrate how to use the FIND and MID functions and how you can combine these functions to extract portions of text.

Formula explanation

The last example from the image above uses a combination of the MID and FIND functions to extract the middle name Steven from the full name Andrew Steven James .

Formula used:

=MID(A7,FIND(" ",A7)+1,FIND(" ",A7,FIND(" ",A7)+1)-FIND(" ",A7))

The first objective is to find the second space and hence where the middle name ends. This is achieved with:

FIND(" ",A2,FIND(" ",A2)+1)

The second objective is to find the first space and hence where the middle name starts. This is achieved with:

FIND(" ",A7)+1

Next, we calculate the length of the middle name by subtracting the number of characters representing the end of the name from the number of characters representing the start of the name:

FIND(" ",A2,FIND(" ",A2)+1)-FIND(" ",A7)

This is now our num_chars argument for the MID function.

When we put the formulas together as arguments in the MID function, we get the following formula that will extract a middle name of any length as long as it is separated by a space.

=MID(A7,FIND(" ",A7)+1,FIND(" ",A7,FIND(" ",A7)+1)-FIND(" ",A7))

The benefit of using a formula like this is that you create it once and use the Fill Handle of the first cell to copy the formula to the other cells. The formula will work for any combination of first name, middle name, and last name as long as they are separated by a space.