On Mon, 21 Dec 2009 09:21:01 -0800, april
wrote:

i have many (over 8000) cells with names and i want to isolate the last name.
the problem is that the format varies. Possible formats

1. A. Sharp
2. Allison Sharp
3. Dr. Allison Sharp
4. Dr and Mr Allison sharp
5. Capt. Allison Sharp, USN, Ret

i could give more examples, but i think that you get the idea. i have tried
text to columns but because there is not pattern this isn't very efficient.

thanks in advance for your help

From what you post, it appears that the last name is either the last word in
the string, or it is the first word that is followed by a comma.

That being the case:

=TRIM(RIGHT(SUBSTITUTE(LEFT(TRIM(A1)&",",
FIND(",",TRIM(A1)&",")-1)," ",REPT(" ",99)),99))

--ron