It happens many times that we wish to find/extract the middle name from a full name. Excel makes this work very easy through its inbuilt functions. One such function is TRIM. With the help of TRIM function along with MID and LEN functions of MS Excel, you can very easily extract the middle name from the given full name.
TRIM is an inbuilt function of Excel that removes all the unnecessary spaces. It then returns the whole string together without any spaces (leading or trailing spaces).
- Text – This is the word from which extra spaces (leading and trailing) are required to be removed.
In this situation, we’ll use TRIM along with LEN and MID.
=TRIM(MID(name,LEN (first)+1,LEN (name)-LEN( first & last))).
This is a tricky formula, so let us understand it with an example.
Let us take an Excel worksheet as shown. It contains a column with names and three columns as shown.
Figure 1: Explanation of TRIM function with MID and LEN
Here, we have listed full names, first name, and last names in column B, C, and D respectively. We extract the middle name in column E. We use the formula as shown in the figure.
The formula used in E5:
LEN function finds the total length of the string while the MID function extracts the specified number of middle characters from the given string. Here, it extracts the text after the last character of the first name and before the first character of the last name.
Length of characters extracted = Length of the full name – (Length of the first name + Length of the last name)
In this way, we get the text between first and the last name. So, we get the middle name as shown in E5.