extracting last name

Try this with the nhame in D2

=IF(ISERR(FIND(" ",D2)),D2,MID(D2,FIND("^^",SUBSTITUTE(D2," ","^^",LEN(D2)-LEN(SUBSTITUTE(D2," ",""))))+1,1024))

--
Regards Ron de Bruin
http://www.rondebruin.nl


"maryj" <maryj@discussions.microsoft.com> wrote in message news:AEB62AF7-278A-4886-9BCF-3E680C3779BE@microsoft.com...
>I have names of individuals in Column A. Some have only first and last,
> others also include the middle initial. I want to extract the last name of
> each person into Column B. I used the formula =RIGHT(A1,LEN(A1)-SEARCH("
> ",A1)) which works great for those names that don't include the middle
> initial. How can I modify this to work for either situation? Thanks!!!
> --
> maryj

=MID(A1,LOOKUP(2,1/(MID(" "&A1,ROW(INDIRECT("1:1024")),1)="
"),ROW(INDIRECT("1:1024"))),1024)

"maryj" <maryj@discussions.microsoft.com> wrote in message
news:AEB62AF7-278A-4886-9BCF-3E680C3779BE@microsoft.com...
>I have names of individuals in Column A. Some have only first and last,
> others also include the middle initial. I want to extract the last name of
> each person into Column B. I used the formula =RIGHT(A1,LEN(A1)-SEARCH("
> ",A1)) which works great for those names that don't include the middle
> initial. How can I modify this to work for either situation? Thanks!!!
> --
> maryj

Try...

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

Hope this helps!

In article <AEB62AF7-278A-4886-9BCF-3E680C3779BE@microsoft.com>,
"maryj" <maryj@discussions.microsoft.com> wrote:

> I have names of individuals in Column A. Some have only first and last,
> others also include the middle initial. I want to extract the last name of
> each person into Column B. I used the formula =RIGHT(A1,LEN(A1)-SEARCH("
> ",A1)) which works great for those names that don't include the middle
> initial. How can I modify this to work for either situation? Thanks!!!

Thanks Ron - that worked! Can you explain what the ^^ mean and why the 1024
at the end?
--
maryj


"Ron de Bruin" wrote:

> Try this with the nhame in D2
>
> =IF(ISERR(FIND(" ",D2)),D2,MID(D2,FIND("^^",SUBSTITUTE(D2," ","^^",LEN(D2)-LEN(SUBSTITUTE(D2," ",""))))+1,1024))
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "maryj" <maryj@discussions.microsoft.com> wrote in message news:AEB62AF7-278A-4886-9BCF-3E680C3779BE@microsoft.com...
> >I have names of individuals in Column A. Some have only first and last,
> > others also include the middle initial. I want to extract the last name of
> > each person into Column B. I used the formula =RIGHT(A1,LEN(A1)-SEARCH("
> > ",A1)) which works great for those names that don't include the middle
> > initial. How can I modify this to work for either situation? Thanks!!!
> > --
> > maryj
>
>
>

I have names of individuals in Column A. Some have only first and last,
others also include the middle initial. I want to extract the last name of
each person into Column B. I used the formula =RIGHT(A1,LEN(A1)-SEARCH("
",A1)) which works great for those names that don't include the middle
initial. How can I modify this to work for either situation? Thanks!!!
--
maryj

Try this with the nhame in D2

=IF(ISERR(FIND(" ",D2)),D2,MID(D2,FIND("^^",SUBSTITUTE(D2," ","^^",LEN(D2)-LEN(SUBSTITUTE(D2," ",""))))+1,1024))

--
Regards Ron de Bruin
http://www.rondebruin.nl


"maryj" <maryj@discussions.microsoft.com> wrote in message news:AEB62AF7-278A-4886-9BCF-3E680C3779BE@microsoft.com...
>I have names of individuals in Column A. Some have only first and last,
> others also include the middle initial. I want to extract the last name of
> each person into Column B. I used the formula =RIGHT(A1,LEN(A1)-SEARCH("
> ",A1)) which works great for those names that don't include the middle
> initial. How can I modify this to work for either situation? Thanks!!!
> --
> maryj

=MID(A1,LOOKUP(2,1/(MID(" "&A1,ROW(INDIRECT("1:1024")),1)="
"),ROW(INDIRECT("1:1024"))),1024)

"maryj" <maryj@discussions.microsoft.com> wrote in message
news:AEB62AF7-278A-4886-9BCF-3E680C3779BE@microsoft.com...
>I have names of individuals in Column A. Some have only first and last,
> others also include the middle initial. I want to extract the last name of
> each person into Column B. I used the formula =RIGHT(A1,LEN(A1)-SEARCH("
> ",A1)) which works great for those names that don't include the middle
> initial. How can I modify this to work for either situation? Thanks!!!
> --
> maryj

Try...

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

Hope this helps!

In article <AEB62AF7-278A-4886-9BCF-3E680C3779BE@microsoft.com>,
"maryj" <maryj@discussions.microsoft.com> wrote:

> I have names of individuals in Column A. Some have only first and last,
> others also include the middle initial. I want to extract the last name of
> each person into Column B. I used the formula =RIGHT(A1,LEN(A1)-SEARCH("
> ",A1)) which works great for those names that don't include the middle
> initial. How can I modify this to work for either situation? Thanks!!!

Thanks Ron - that worked! Can you explain what the ^^ mean and why the 1024
at the end?
--
maryj


"Ron de Bruin" wrote:

> Try this with the nhame in D2
>
> =IF(ISERR(FIND(" ",D2)),D2,MID(D2,FIND("^^",SUBSTITUTE(D2," ","^^",LEN(D2)-LEN(SUBSTITUTE(D2," ",""))))+1,1024))
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "maryj" <maryj@discussions.microsoft.com> wrote in message news:AEB62AF7-278A-4886-9BCF-3E680C3779BE@microsoft.com...
> >I have names of individuals in Column A. Some have only first and last,
> > others also include the middle initial. I want to extract the last name of
> > each person into Column B. I used the formula =RIGHT(A1,LEN(A1)-SEARCH("
> > ",A1)) which works great for those names that don't include the middle
> > initial. How can I modify this to work for either situation? Thanks!!!
> > --
> > maryj
>
>
>