Combine First and Last Name Ignoring Middle Name

How to Combine First and Last Name Ignoring Middle Name in Microsoft Excel

If you have a list of names in all sort of different formats & you are supposed to quickly extract a list of names; in case you are facing a similar problem in combining first & last name without middle name or initial then this article is for you. In this article, we will learn how to combine first & last name in different formats.

Question): I tried so many formulas to combine in order to extract first & last name only from full name after skipping middle name & the problem is the list of names is not in the same format otherwise it will be a lot easier; the format of name is different to other one.

Following is the snapshot of data we have; the first column has full name & the second column has the preferred output:

We will use a combination of IF, ISNUMBER, FIND, LEFT, MID & SUBSTITUTE functions to find the result.
In cell B2, the formula is
=IF(ISNUMBER(FIND("-",A2,1)),LEFT(A2,FIND("-",A2,1)-1),LEFT(A2,FIND(" ",A2,1)-1)&MID(A2,FIND("@",SUBSTITUTE(A2," ","@",2),1),99))

There are basically three different formats of names we have
Cell A2 has “L.” as middle name & cell A3 has “Bryan” while cell A4 has “L” at the end position.
The above formula has to take care of all the conditions & return the prefer output.

In this way, we can separate first & last name from one cell & then extract in another cell meeting set of name formats.