Help with: =RIGHT(A2,LEN(A2)-FIND(" ",A2))

[Andy B]

Hi

Try this:
=LEFT(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))-1)
for the first name/s
and this:
=RIGHT(A1,LEN(A1)-FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))
for the surname.

 

[Peo Sjoblom]

One way

=RIGHT(A1,LEN(A1)-SEARCH(CHAR(190),SUBSTITUTE(A1,"
",CHAR(190),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

that will extract the last name unless there is a JR or II or something,
those you need to
do manually

Assume you extract the last names in column G, now in another column use

=SUBSTITUTE(A1,G1,"")

and copy down

 

[Andy B]

Hi

Try these:
=IF(ISERROR(FIND(" ",A1)),A1,LEFT(A1,FIND("^",SUBSTITUTE(A1,"
","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1))

and

=IF(ISERROR(FIND(" ",A1)),"",RIGHT(A1,LEN(A1)-FIND("^",SUBSTITUTE(A1,"
","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

 

[Stunners]

Ok, Am trying to split up Full Names in a cell. I got this formula from MS's
training section of their website.
It works great when the name is John Smith. Problem is when the guy's name
is something like John A. Smith. It only recognizes the first space, and
counts the last name from there.

My names have as many as 2 or 3 spaces (John and Sarah Smith).
Can someone please help me?

Thanks!
Yoni

 

[Stunners]

Thanks Man, worked perfectly.
Since that seemed to be too easy for you, how about throwing something in
there that:
If there is only 1 name, we will assume its a first name, so copy it over
when checking for first names, and give a blank response for the Last Name.

Say, original name is Amanda, Have new First Name field show Amanda, and
have the Last Name field be empty (but errorfree).

If it too big of a deal, then don't worry about it.
Again, huge thanks!!
Yoni

 

[Stunners]

Thanks, but I already used Andy's answer.

One way

=RIGHT(A1,LEN(A1)-SEARCH(CHAR(190),SUBSTITUTE(A1,"
",CHAR(190),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

that will extract the last name unless there is a JR or II or something,
those you need to
do manually

Assume you extract the last names in column G, now in another column use

=SUBSTITUTE(A1,G1,"")

and copy down

--

Regards,

Peo Sjoblom

 

[Stunners]

Thanks

Hi

Try these:
=IF(ISERROR(FIND(" ",A1)),A1,LEFT(A1,FIND("^",SUBSTITUTE(A1,"
","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1))

and

=IF(ISERROR(FIND(" ",A1)),"",RIGHT(A1,LEN(A1)-FIND("^",SUBSTITUTE(A1,"
","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))