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

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

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.

--
Andy.


"Stunners" <massuds2000@yahoo.com> wrote in message
news:9NJoc.6484$ol3.3719@fe20.usenetserver.com...
> 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
>
>
>
>

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" <massuds2000@yahoo.com> wrote in message
news:9NJoc.6484$ol3.3719@fe20.usenetserver.com...
> 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
"Andy B" <andyb@takethisbitout.dawsons.co.uk> wrote in message
news:%23thcCgOOEHA.640@TK2MSFTNGP12.phx.gbl...
> 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.
>
> --
> Andy.
>
>
> "Stunners" <massuds2000@yahoo.com> wrote in message
> news:9NJoc.6484$ol3.3719@fe20.usenetserver.com...
> > 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, but I already used Andy's answer.

"Peo Sjoblom" <terre08@mvp.org> wrote in message
news:OU$sklOOEHA.3708@TK2MSFTNGP10.phx.gbl...
> 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" <massuds2000@yahoo.com> wrote in message
> news:9NJoc.6484$ol3.3719@fe20.usenetserver.com...
> > 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
> >
> >
> >
> >
>
>

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," ",""))))))

--
Andy.


"Stunners" <massuds2000@yahoo.com> wrote in message
news:xhKoc.14098$8P4.5891@fe09.usenetserver.com...
> 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
> "Andy B" <andyb@takethisbitout.dawsons.co.uk> wrote in message
> news:%23thcCgOOEHA.640@TK2MSFTNGP12.phx.gbl...
> > 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.
> >
> > --
> > Andy.
> >
> >
> > "Stunners" <massuds2000@yahoo.com> wrote in message
> > news:9NJoc.6484$ol3.3719@fe20.usenetserver.com...
> > > 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
"Andy B" <andyb@takethisbitout.dawsons.co.uk> wrote in message
news:OMstIvOOEHA.2468@TK2MSFTNGP11.phx.gbl...
> 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," ",""))))))
>
> --
> Andy.
>
>
> "Stunners" <massuds2000@yahoo.com> wrote in message
> news:xhKoc.14098$8P4.5891@fe09.usenetserver.com...
> > 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
> > "Andy B" <andyb@takethisbitout.dawsons.co.uk> wrote in message
> > news:%23thcCgOOEHA.640@TK2MSFTNGP12.phx.gbl...
> > > 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.
> > >
> > > --
> > > Andy.
> > >
> > >
> > > "Stunners" <massuds2000@yahoo.com> wrote in message
> > > news:9NJoc.6484$ol3.3719@fe20.usenetserver.com...
> > > > 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
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >
>
>