Excel MID Function Examples

When dealing with real-life tasks in Excel, you will often find the MID Function handy when combined with other functions, as demonstrated in the following examples:

How to Extract First and Last Names

We already know how to get the first name using the LEFT Function and get the last name with the RIGHT Function. Below are some examples of extracting the values.

MID Formula to Get the First Name

To extract the first name from the full name in cell A2, you can use the following formula:

=MID(A2, 1, SEARCH(” “,A2)-1)

Note: The SEARCH Function scans the original string (A2) for the space character(” “) and returns its position. By subtracting 1, we avoid capturing trailing spaces. Then the ‘MID’ Function extracts a substring from the first character and spans up to the character preceding the space, effectively fetching the First name.

MID Formula to get the Last Name

Use the formula to Extract the last name from the cell:

=TRIM(MID(A2, SEARCH(” “,A2), LEN(A2)))

Note: The ‘SEARCH’ Function is used to determine the starting position (the space character). Since we don’t need to specify the exact end position, we can use ‘LEN(A2)’ to represent the total length of the original string. The ‘TRIM’ Function is then used to remove any extra spaces that might appear before or after the extracted last name.

Example:

Here, we used the MID function 3 times on cells, i.e. on MAYA, RAJ, and SHAUN. Now, we will see individual 3 formulas for these three:

1. In cell A2(MAYA), we applied–> MID(A2,1,2): Here, we used A2 as our text to be sliced. We will start from the 1st index, ie the very first element, which is “M”.The length of the string is kept as 2, so the string will be sliced till the 2nd index of the string, which is “A”. This will result in the output “MA”.
2. In cell A3(RAJ), we applied–> MID(A3,2,1): Here, we used A3 as our text to be sliced. We will start from the 2nd index, which is “A”.The length of the string is kept as 1, so the sliced string will be till this index only. This will result in the output “A”.
3.  In cell A4(SHAUN), we applied–> MID(A4,1,3):  Here, we used A4 as our text to be sliced. We will start from the 1st index, ie the very first element, which is “S”.The length of the string is kept at 3, so the string will be sliced till the 3rd index of the string, which is “A”.  This will result in the output “SHA”.

It is not necessary to provide just a word to be sliced, we can also use a sentence, that can be sliced by using the MID function. The only thing to keep in mind is that the spaces between the words in a sentence will also be counted as an index. 

Example 2:

Here we applied the MID function to the A3 cell, which is a sentence, and we obtained a sliced string, which is between the A3 and the B3 cell. Now, let us see how the MID function was applied.

=MID(A3,5,14)

Here, the MID function will slice the string stored in the A3 column. The starting point of slicing is from the 5th index, which is “s”.Now, as previously discussed, the spaces between the words in a sentence, also count as an index, so the length of 14 also included the space between “s” and “t”. Therefore the outcome is “for geeks is t”.

If you use programming languages, like Python, you must have used a function called string slicing, which extracts a part of the string and can directly print it, or store a result. The same functionality can be achieved in Excel when we use Excel’s MID function.

The MID function can return a part of the string provided to it. Let us see the workings of this function.