Get middle name from full name

Get-middle-name-from-full-name

Learn how to get the middle name from a full name using the MID, LEN, and TRIM functions to split the middle name.

This article will explain the traditional method using built-in Excel text functions like MID, LEN, and TRIM and then introduce the most straightforward method using user-defined functions.

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, this formula seems a bit complex. 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
Get-middle-name-from-full-name

Use the following formula in cell H3:

=TRIM(MID(B3,LEN(D3)+1,LEN(B3)-LEN(D3&F3)))

For reasons of simplification, we’ll split the formula.

=MID(B3,LEN(D3)+1

The MID function removes text from the full name. It starts at the first character and continues until the length of the first name.

=MID(B3,LEN(D3)+1,LEN(B3)-LEN(D3&F3))

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:

  1. The MID function gets the middle name (with spaces on the left and right sides), and TRIM removes the extra space.
  2. What about multiple middle names? If the original string contains multiple middle names, the MID function will display all of them. As mentioned above, the TRIM cuts the extra spaces from both sides of the middle name.
  3. 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 GETWORDS function, our favorite secret elixir! We want to save you time and use the fastest method.

getwords function

Syntax and arguments:

=GETWORDS(cell, n, delimiter)

  • cell: The reference to the cell containing the text string (e.g., a full name).
  • n: The position of the word you want to extract (e.g., 2 for the second word).
  • Delimiter: The character that separates the words (commonly a space ” “).

GETWORDS simplifies retrieving specific words from a string, such as extracting a middle name from a full name.

Note: Use our Excel add-in to speed up your data-cleaning task! You can use hundreds of user-defined functions to quickly get the first or last name from a full name.

Additional resources: