# 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

### Taking Out Middle Names On A Long List Of Names

Rate this:
(3/5 from 1 vote)
 HiMy name is RobertI'm new to this forum and would very much like someone to assist me.INFORMATION I HAVEI 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 GodingsI 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 GodingsPROBLEMWhat 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?ASSUMPTIONAssuming 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 joe8489 on 06 Jun 2011
 Replies - Displaying 1 to 5 of 5 Order Replies By: Most Recent | Chronological | Highest Rated
 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 Excel Helper on 06 Jun 2011
 Rate this: (3/5 from 1 vote) Hi there Excel HelperThe 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 joe8489 on 07 Jun 2011
 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 Excel Helper on 07 Jun 2011
 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 endWhen 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 joe8489 on 07 Jun 2011
 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 Excel Helper on 07 Jun 2011
 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 Excel Business Solutions Directory