How to get the middle name from a full name? We’ll show you various formulas for splitting the middle name. Use the combination of MID, LEN, and TRIM function!
In this article, we’ll differ from the normal way and introduce the easiest method first. Don’t worry; later, we’ll explain the regular method using built-in Excel functions.
Get the middle name from the name using VBA
In the example, we’ll use the Extract_Nth_Word function!
This is our favorite elixir! We want to save your time and using the fastest method.
Let’s see the syntax and the arguments!
We have to add only two parameters to get the middle name:
- cell_reference: the position of the cell which contains the full name
- nth: the number of the word in the string, that we want to extract
We’ll publish the source code soon. Meanwhile, use our free Excel add-in! Use the TextLeft and TextRight user-defined functions. Both of them are available in DataFX, our free UDF library.
If you have time enough, read the basic example below and use the regular functions.
Generic Formula to extract the middle name
It seems this formula a little bit outdated.
Before we start the string manipulation, we need to get the first and the last name.
In the example, our initial data set contains the following:
- we have the full name in column A
- column B contains the first name
- the last name placed in column C
Use the formula that looks like this:
Let’s see the main point of the formula! For reasons of simplification, we’ll split the formula.
The MID function cut text from the full name out. It starts from the first character until the length of the first name.
Let’s examine the following formula which gives the equivalence.
Total characters extracted = length of the full name – (length of the first and last names)
Tip: Formulas should be drafted in plain language.
All right, 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? If we have multiple middle names in the original string, the MID function will display all middle names. As we mentioned above, the TRIM cuts the extra spaces from both sides of the middle name.
- When the middle name does not exist in the full name the formula returns with an empty string.