Learn how to get the middle name from a full name using the MID, LEN, and TRIM functions for splitting the middle name.
This article will explain the traditional method using built-in Excel text functions like MID, LEN, and TRIM. After that, we introduce the most straightforward method using the ExtractNthWord user-defined function.
Generic formula to extract the middle name
Here is the formula that uses regular Excel functions:
=TRIM(MID(full_name,LEN(first_name)+1,LEN(full_name)-LEN(first_name&last_name)))Unfortunately, it seems this formula is a little bit complex. Note: We must get the first and last names before extracting the middle name.
Explanation
In the example, our initial data set contains the following:
- we have the full name in column B
- column D includes the first name
- the last name placed in column F
Use the following formula in cell H3:
=TRIM(MID(B3,LEN(D3)+1,LEN(B3)-LEN(D3&F3)))Let’s see the main point of the formula! For reasons of simplification, we’ll split the formula.
=MID(B3,LEN(D3)+1The MID function cut text from the full name out. It starts from the first character until the length of the first name.
=MID(B3,LEN(D3)+1,LEN(B3)-LEN(D3&F3))Let’s translate the formula above:
Total characters extracted = length of the full name – (length of the first and last names)
The formula extracts all text between the first name and the last name. What about extra spaces? The result contains additional spaces. To remove them, we’ll use the TRIM function.
The possible outputs:
- The MID function gets the middle name (with space on the left and the right side), and TRIM removes the extra space.
- What about multiple middle names? The MID function will display all middle names if we have multiple middle names in the original string. As mentioned above, the TRIM cuts the extra spaces from both sides of the middle name.
- The formula returns with an empty string when the middle name does not exist in the full name.
Get the middle name from the name using VBA
In the example, we’ll use the Extract_Nth_Word function, our favorite secret elixir! We want to save you time and use the fastest method.
Let’s see the syntax and the arguments!
Syntax:
=Extract_Nth_Word(text, nth)Arguments:
- text: cell reference; the position of the cell which contains the full name
- nth: an integer value; the number of the word in the string that we want to extract
Use our Excel add-in if you want to speed up your data-cleaning task! Note: you can use the TextLeft and TextRight user-defined functions to quickly get the first or last name from a full name. Both of them are available in DataFX, our free UDF library.
