return right most word

[Guest]

i have cells with names in them, however, i want to separate the name so that the first name is in one cell and the surname is displayed in a neigbouring cell.

to return the left most word (first name) i used

=LEFT(A2,FIND(" ",A2,1)-1)

how do i manipulate the formula to return the rightmost word (i.e. surname)

thanx for your help

 

[Frank Kabel]

Hi
if you only have names without middle names use
=MID(A2,FIND(" ",A2)+1,255)

-----Original Message-----
i have cells with names in them, however, i want to

separate the name so that the first name is in one cell
and the surname is displayed in a neigbouring cell.

 

[Peo Sjoblom]

There are different ways, since you already know how to extract the first
name you could use
that to extract the last, assume you put the first name formula in B2, then
in C2 you can use

=TRIM(SUBSTITUTE(A2,B2,""))

meaning that you replace the first name with "nothing" thus returning what's
left

with more complicated names and to always return the last string separated
by a space you can use

=RIGHT(A2,LEN(A2)-SEARCH("^^",SUBSTITUTE(A2,"
","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

However I would go with the first solution


--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

i have cells with names in them, however, i want to separate the name so

that the first name is in one cell and the surname is displayed in a
neigbouring cell.