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)+1`

The 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 r
**eturns 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.