Extracting From A Name String

[isadoko]

OS: XP
XL: 2002

Need help extracting ONLY first and last names from strings such as these:

Deborah P. Sloan
John Mark P Cooledge
Kami (Kamisaki) Tomodachi

First name so far no problem using:
=UPPER(LEFT(D2,IF(ISERROR(FIND(" ",D2,1)),LEN(D2),FIND(" ",D2,1)-1)))

Last name extraction no successful using:
=TRIM(RIGHT(D2,LEN(D2)-IF(ISERROR(FIND(" ",D2,FIND(" ",D2,FIND(" ",D2,1)+2))),LEN(D2),FIND(" ",D2,FIND(" ",D2,FIND(" ",D2,1)+2))-1)))

This formula only works if applied to simple first and last combo. Would much appreciate a solution.

 

[Oaktree]

First Name =LEFT(A1,SEARCH(" ",A1)-1)

Last Name =RIGHT(A1,LEN(A1)-SEARCH("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

 

[isadoko]

Oaktree

Much obliged for your response yet my trial using your solution for extracting last name failed: got "#Value!". Pardon my lack of knowledge but am I to swap something for the @ in your formula or do they serve a wild card function? Else uncertain the reason for my error. Please clarify.

isadoko

 

[Oaktree]

@ was just a character that wouldn't occur in a name. You could use ! or # or ^ etc.

What's the exact formula you're using that's returning #VALUE!?

 

[barry houdini]

Another option to return the last name.....

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

...also for first name

=LEFT(A1,FIND(" ",A1&" ")-1)

both of these formulas should return a blank cell if A1 is blank

 

[isadoko]

Oaktree,

I used

=RIGHT(D28,LEN(D28)-SEARCH("@",SUBSTITUTE(D28," ","@",LEN(A1)-LEN(SUBSTITUTE(D28," ","")))))

on: Paul Martine P Duvalier

isadoko

 

[isadoko]

Barry,

I successfully applied your formulas. Much obliged.

isadoko

 

[Oaktree]

Looks like you have solved your problem.

FWIW, I think you just had multiple cell references in your formula (D28 in all but one reference, which was A1)

If Paul Martine P Duvalier is in A1, =RIGHT(A1,LEN(A1)-SEARCH("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) would work.