Get middle name from full name

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 function

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

Syntax

=TRIM(text)

Explanation

Arguments

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

Generic formula

=TRIM(MID(name,LEN (first)+1,LEN (name)-LEN( first & last))).

This is a tricky formula, so let us understand it with an example.

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:

=TRIM(MID(B5,LEN(C5)+1,LEN(B5)-LEN(C5&D5)))

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.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar