Excel formula: Get middle name from full name

Get middle name from full name

Figure 1. of Get Middle Name from Full Name in Excel.

In the event that we are required to obtain a middle name from a full name, we are going to use the LEN and MID Functions as well as the TRIM Function. This tutorial will step through the process

Generic Formula

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

How to Use the Excel LEN, MID and TRIM Functions in Excel.

We can achieve this by following three simple steps!

Let us assume that we have a person’s full name in our Excel sheet column A, the first name in another column B, the last name in a third column C. See example illustrated below;

Figure 2. of Full name in Excel sheet.

Label the next column D as “MIDDLE NAME”. This is where Excel will return the Middle Name data value. The formula syntax which we will enter into the formula bar for cell D2 in the example illustrated below, is as follows;

=TRIM(MID(A2,LEN(B2)+1,LEN(A2)-LEN(B2&C2)))

Figure 3. of LEN, MID and TRIM Functions in Excel.

The Excel MID Function will start by extracting text from our full name text in column A, beginning from 1 character which comes after the first name. The Excel TRIM Function then begins the operation of clearing out any extra spaces or unwanted characters.

Copy the formula in cell D2 of our worksheet illustration into the other cells in column D to get the desired results.

Figure 4. of LEN, MID and TRIM Functions in Excel.

Note

If there is an existing middle name, the Excel MID Function will extract this middle name then the Excel TRIM Function proceeds to eliminate any extra space.
In the event that we have more than one existing middle name, the Excel MID Function will extract all these middle names and then the Excel TRIM Function proceeds to eliminate any extra space.
If there happens to be no existing middle name, the Excel MID Function will automatically return a blank/space character which the Excel TRIM Function will proceed to eliminate.

Figure 5. of Final Result.

Instant Connection to an Expert through our Excelchat Service

Our live Excelchat Service is here for you. We have Excel Experts available 24/7 to answer any Excel questions you may have. Guaranteed connection within 30 seconds and a customized solution for you within 20 minutes.