Excel MID Function – How to Use

Excel MID function is a replica of the substring function that you may have studied in programming languages. In simple words, this formula is used to extract a small part of the string from the input string or we can also define it as a function that returns a specified number of characters from the supplied string.

How Excel defines MID Function

Microsoft Excel defines the MID function as a formula that “Returns the characters from the middle of a text string, given a starting position and length.”

This means that we have to supply MID function three things – 1. The source string 2. The starting position for extracting the substring and 3. The length of the resultant string.

Syntax of Excel MID Function

The syntax of the MID function is as under:

=MID(Input_string, Start_position, Number_of_characters)

‘Input_string’ signifies the string from which you want to extract a substring.
‘Start_position’ signifies the numerical position in the ‘Input_string’ from where you want to begin the extraction.
‘Number_of_characters’ indicates the number of characters that you wish to extract.

Few Important things about MID Formula

  • In MID Function the value of ‘Start_position’ should always be greater than ‘0’. If you enter its value less than or equal to ‘0’ then it results in a #VALUE! error.
  • In the MID function, the value of ‘Number_of_characters’ should always be greater than ‘0’. If its value is equal to ‘0’ then the MID function will not extract any character and its output will be blank. If its value is less than ‘0’ then MID Function throws a #VALUE! error.

Examples of MID Formula

Excel MID Function

In the above example I have used two MID functions:

1. In the first function I have used the MID formula as:

=MID(A1,1,4)

So this formula starts extracting the substring from the input string present at ‘A1’. The extraction begins at position 1 and ends after 4 characters and hence the result a substring “This”.

2. In the second function the MID formula is used as:

=MID(A3,6,5)

In this example, the substring is extracted from the text present in the ‘A3’ cell and the extraction begins from the 6th position and ends after 5 characters and that’s why the resultant substring is “is an”.

A better way to perform substrings using the MID function

As you have already seen that MID function works in a pretty hardcoded manner. By hardcoded, I mean in MID you have to specify the start position and the number of characters yourself. What if, you have a list of people's names along with their surnames separated by a space? How will you extract a person’s first name from such a list?

Now in such a case only using the MID function is not going to help you as you cannot be sure about the number of characters in a person’s name or surname. In such situations, Search Function can be quite helpful. It helps you to create better and sophisticated substring formulas.

If you are not aware of the SEARCH formula then probably you should read this post.

MID function with Search Function example

In the above example, I have used the MID function along with Search to extract a person’s First and Last name.

1. For Extracting First Name of a Person: I have used the formula as:

=MID(A1,1,SEARCH(" ",A1,1))

In this example, the MID function searches the string at the A1 position and starts the substring operation from the 1st character of the input string. For the length of the extracted string, I have used Search Function which scans the string at A1 cell for space (“ ”) character and returns its position as an integer value. And thus the result of both these functions fetches the person’s name.

2. For Extracting the Last Name of a Person: In the second part to extract the last name of a person I have used the formula as:

=MID(A1,SEARCH(" ",A1),100)

In this formula, the MID function extracts from the cell no. A1. The start position is decided by the Search formula, here the Search formula gives the position of space character (“ ”), and hence extraction starts from there and continues till the end of the string.

The above function also includes a space (“ ”) character at the start of the Last name substring so a better option would be to use the MID function as:

=MID(A2,SEARCH(" ",A1)+1,100)
About Ankit Kaul

Ankit is a die-hard fan of Microsoft Excel and has been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'.