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.

 

[njimack]

=right(a1,len(a1)-find(" ",a1))

 

[bkaehny]

=right(a1,len(a1)-find(" ",a1))

For people with a middle initial or multiple middle names, that formula returns the middle initial or name as well as the last name. I need something that will give just the last name.

 

[njimack]

My bad. Give this a go...
=TRIM(RIGHT(A1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

 

[bkaehny]

My bad. Give this a go...
=TRIM(RIGHT(A1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

I get the same result as the other formula

 

[bkaehny]

Bump.

 

[njimack]

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

 

[bkaehny]

Perfect! Thanks!

One question on your solution: what is the purpose of substituting # for a space? I understand your formula until you get to that part.

 

[njimack]

Perfect! Thanks!

One question on your solution: what is the purpose of substituting # for a space? I understand your formula until you get to that part.

What we actually do is substitute the LAST space, using

LEN(A1)-LEN(SUBSTITUTE(A1," ",""))

This lets us find all text after the last space.

 

[bkaehny]

That's beautiful. Thank you again for your help!