Finding last name problem

[hatstand]

I'm trying to amend this formula to find the last name in a cell that has the a fist name and second name, as copied from cpearson, a big thank you too him :

=TRIM(RIGHT(K239,LEN(K239)-IF(ISERROR(FIND(" ",K239,FIND(" ",K239,FIND(" ",K239,1)+2))),LEN(K239),FIND(" ",K239,FIND(" ",K239,FIND(" ",K239,1)+2))-1)))

I have already amended it to look for spaces rather than "," seperators. But it will only work when there is a middle name. Is it possible to change it to find the last name only?

Any help would be much appreciated.

 

[pgc01]

Hi hatstand

Assuming the last name comes after a space, this formula will extract it no matter how many names the person has:

=RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

Hope this helps
PGC

 

[Andrew Poulsom]

If A1 contains John Smith or John Peter Smith (or even Mr John Perter Smith) this:

=RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

will return Smith.

 

[hatstand]

A big thanks you

Thanks for your help. They work perfectly.

 

[barry houdini]

Here's an alternative, assuming your surname has no more than 99 letters

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99))