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:

 

img1

 

  • 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))

 

img2

 

  • 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.

image 7

Download – How to combine first and last name ignoring middle name – xlsx



Example:


Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>