Formula to return the last word in a cell

[bkaehny]

Hi all,

I have a column containing full names of people, where each part of their name is separated by a space (e.g. John Smith, John Q Public, Herald van der Schnossen). Some of these names contain middle initials or middle names, some contain just the first and last name, and some even have multiple middle names. I need to separate out just the last name using a formula. Can this be done? I can envision one the uses some iferrors, mids, and finds, but it would quickly become long and cumbersome to adjust. Any ideas?

Thanks.

I'm using Excel 2010, btw.

 

[mmodroger]

one other option:

Excel Workbook
	A	B
1	John Smith	Smith
2	John Q Public	Public
3	Herald van der Schnossen	Schnossen
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=TRIM(MID(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),99))
B2	=TRIM(MID(A2,FIND("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))),99))
B3	=TRIM(MID(A3,FIND("^",SUBSTITUTE(A3," ","^",LEN(A3)-LEN(SUBSTITUTE(A3," ","")))),99))