Extract last name from a list that contains first, middle and last names

[bearcub]

I have list of full names. some names have first name only, and some have first and middle name. I would like to use a formula that will extract the last name from all of them. I know that flash fill could do it but I need a formula because the list is always updating.

For example

Name                       Extract Value
Jon Jay Jones                Jones
Mary Smith                   Smith
Jose A Jones Garcia       Garcia
Bill Graham                  Graham

is this possible?

Thank you for your help in advance

Michael

 

[Dryver14]

=right(h3,len(h3)-find("*",substitute(h3," ","*",len(h3)-len(substitute(h3," ","")))))

 

[jtakw]

Hi,

Here's another way:

Book1
	A	B	C
1	Name		Extract Value
2	Jon Jay Jones		Jones
3	Mary Smith		Smith
4	Jose A Jones Garcia		Garcia
5	Bill Graham		Graham
Sheet2

Cell Formulas
Range	Formula
C2	=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",200)),100))

 

[bearcub]

Great, thank you both, this will work. I'll add this to my file when I get back to work on Monday.

Michael