Home > Forum Home > Automating Data Analysis Tasks > Taking out middle names on a long list of names Share

Taking out middle names on a long list of names

Excel Help for Taking Out Middle Names On A Long List Of Names in Automating Data Analysis Tasks


Forum TopicPost Reply Login

Taking Out Middle Names On A Long List Of Names

Rate this:
(3/5 from 1 vote)
ConfusedHi

My name is Robert

I'm new to this forum and would very much like someone to assist me.

INFORMATION I HAVE

I have a long list of names on one column. Some names are just made up of first name and surname. Others have a middle name, sometimes even two middle names.

This is what the list looks like (the first 7 of 45,000 names). Need to get rid of the middle names (italic, underlined):

Aaran Naddan
Aaron Bryce Tornelle
Aaron Gibb Bryan Holy
Aaron Howard
Aaron Jonna Peter Ganjel
Aaron Kat
Aaron Lyall Godings

I need the list to look like this (obviously trying to identify all of them within the 45,000 will be quite a task):

Aaran Naddan
Aaron Tornelle
Aaron Holy
Aaron Howard
Aaron Ganjel
Aaron Kat
Aaron Godings

PROBLEM
What I'd like to do is simply delete these middle names so that all I'm left with is the first name and surname.

SOLUTION?

Is there a formula that can identify and delete these middle names?

ASSUMPTION
Assuming of course, that the first name in the cell is the person's first name, and the last name in the cell is infact the person's surname.
Thanks in advance for your help, it'll be great if there is someone out there who can assist me.

Cheers
Robert
 Posted by on
 
Replies - Displaying 1 to 5 of 5Order Replies By: Most Recent | Chronological | Highest Rated
Happy
Rate this:
(3/5 from 1 vote)
You can create a formula to extract the first word (first name) and the last word (last name) and then either keep them in separate columns or append them together in one column.

Suppose that your names are in column A with the first in cell A2.  The following formulas will get the first name and last name.

First Name (first word):
=LEFT(A2,IF(ISERROR(FIND(" ",A2,1)),LEN(A2),FIND(" ",A2,1)-1))

Last Name (last word):
=IF(ISERROR(FIND(" ",A2)),"",RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))

Enjoy.
 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
Hi there Excel Helper

The first formula worked beautifully , the second one however did not.

When I dragged the second formula down, it seemed to only pick up one in every 6 or 7 of the cells in Column A,

Any ideas?
 Posted by on
Oops
Rate this:
(3/5 from 1 vote)
OK - can you give an example or 2 of the cell text where the last name formula does not work?
 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
hey there, just figured it out. Its because 95% of column A (the column im drawing the data from), has cells where the surname is followed by a space. That's why your formula wasnt picking it up.

On closer inspection, it was only picking up the surnames with no space at the end

When i indiviually go and delete the space, the surname magically appears in column C (Column B has the 'firstname' formula) 

is there a formula for deleting that space for all that have one in their after the surname? 
 Posted by on
Sad
Rate this:
(3/5 from 1 vote)
In this case, we need to trim the content of the cell to remove any preceding or trailing spaces.  The last name formula becomes:
=IF(ISERROR(FIND(" ",TRIM(A2))),"",RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND("*",SUBSTITUTE(TRIM(A2)," ","*",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))))
 Excel Business Forums Administrator
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Taking out middle names on a long list of names in the