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

  • Thread starter Thread starter Andy B
  • Start date Start date
A

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.
 
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
 
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," ",""))))))
 
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
 
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
 
Thanks, but I already used Andy's answer.

Peo Sjoblom said:
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
Click to expand...
 
Thanks
Andy B said:
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," ",""))))))
Click to expand...
 
Back
Top