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)

	Hi 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 joe8489 on 06 Jun 2011

Replies - Displaying 1 to 5 of 5

Order Replies By: Most Recent | Chronological | Highest Rated

	Get First and Last Word in cell 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

	THANK YOU 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 joe8489 on 07 Jun 2011

	Example of last word failing 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

JUST FIGURED WHY 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 joe8489 on 07 Jun 2011

	Trimming the content 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