+ Reply to Thread
Results 1 to 9 of 9

Extract last name and insert into a different field.

  1. #1
    Barb
    Guest

    Extract last name and insert into a different field.

    I want to extract a person's last name from a field & put it into another for
    sorting purposes. If their name is Bud Line, I want to put Line into a
    different field. If their name is Betsy Sue Color, I want to put Color into
    a different field. I think their should be a way to search for the space
    starting from the right side, but I don't know how. The problem I'm having
    is if their name is listed as first, middle and last.

  2. #2
    Forum Contributor
    Join Date
    12-14-2005
    Posts
    176
    Work with a copy before doing this:
    Select the column and then on the menu bar select REPLACE:
    In the FIND box - use the space bar and enter 1 space
    In the REPLACE box - enter a comma.
    Then do a replace all
    This should replace all space between the names with a comma.
    Now select the column and then go to the MENU Bar and Select DATA - TEXT TO COLUMNS and follow the instructions.

  3. #3
    Ron de Bruin
    Guest

    Re: Extract last name and insert into a different field.

    Hi Barb

    Try this formula with the name in A2

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

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


    "Barb" <Barb@discussions.microsoft.com> wrote in message news:16C561D5-99D0-4B77-B813-ADD7413D38CF@microsoft.com...
    >I want to extract a person's last name from a field & put it into another for
    > sorting purposes. If their name is Bud Line, I want to put Line into a
    > different field. If their name is Betsy Sue Color, I want to put Color into
    > a different field. I think their should be a way to search for the space
    > starting from the right side, but I don't know how. The problem I'm having
    > is if their name is listed as first, middle and last.




  4. #4
    Barb
    Guest

    Re: Extract last name and insert into a different field.

    Works great except it uses up two lines for the answer & it has a grey
    background.

    "Ron de Bruin" wrote:

    > Hi Barb
    >
    > Try this formula with the name in A2
    >
    > =IF(ISERR(FIND(" ",A2)),A2,MID(A2,FIND("^^",SUBSTITUTE(A2," ","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,1024))
    >
    > --
    > Regards Ron de Bruin
    > http://www.rondebruin.nl
    >
    >
    > "Barb" <Barb@discussions.microsoft.com> wrote in message news:16C561D5-99D0-4B77-B813-ADD7413D38CF@microsoft.com...
    > >I want to extract a person's last name from a field & put it into another for
    > > sorting purposes. If their name is Bud Line, I want to put Line into a
    > > different field. If their name is Betsy Sue Color, I want to put Color into
    > > a different field. I think their should be a way to search for the space
    > > starting from the right side, but I don't know how. The problem I'm having
    > > is if their name is listed as first, middle and last.

    >
    >
    >


  5. #5
    Ron de Bruin
    Guest

    Re: Extract last name and insert into a different field.

    > Works great except it uses up two lines for the answer & it has a grey
    > background.


    I do not understand you ?



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


    "Barb" <Barb@discussions.microsoft.com> wrote in message news:9E773E68-DCD1-409B-9858-8E5AA3C0115A@microsoft.com...
    > Works great except it uses up two lines for the answer & it has a grey
    > background.
    >
    > "Ron de Bruin" wrote:
    >
    >> Hi Barb
    >>
    >> Try this formula with the name in A2
    >>
    >> =IF(ISERR(FIND(" ",A2)),A2,MID(A2,FIND("^^",SUBSTITUTE(A2," ","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,1024))
    >>
    >> --
    >> Regards Ron de Bruin
    >> http://www.rondebruin.nl
    >>
    >>
    >> "Barb" <Barb@discussions.microsoft.com> wrote in message news:16C561D5-99D0-4B77-B813-ADD7413D38CF@microsoft.com...
    >> >I want to extract a person's last name from a field & put it into another for
    >> > sorting purposes. If their name is Bud Line, I want to put Line into a
    >> > different field. If their name is Betsy Sue Color, I want to put Color into
    >> > a different field. I think their should be a way to search for the space
    >> > starting from the right side, but I don't know how. The problem I'm having
    >> > is if their name is listed as first, middle and last.

    >>
    >>
    >>




  6. #6
    Barb
    Guest

    Re: Extract last name and insert into a different field.

    If I put the formula in B1, the last name appears in B1 but C1 becomes a
    merged cell with B1 and both cells have a dark grey background.

    "Ron de Bruin" wrote:

    > > Works great except it uses up two lines for the answer & it has a grey
    > > background.

    >
    > I do not understand you ?
    >
    >
    >
    > --
    > Regards Ron de Bruin
    > http://www.rondebruin.nl
    >
    >
    > "Barb" <Barb@discussions.microsoft.com> wrote in message news:9E773E68-DCD1-409B-9858-8E5AA3C0115A@microsoft.com...
    > > Works great except it uses up two lines for the answer & it has a grey
    > > background.
    > >
    > > "Ron de Bruin" wrote:
    > >
    > >> Hi Barb
    > >>
    > >> Try this formula with the name in A2
    > >>
    > >> =IF(ISERR(FIND(" ",A2)),A2,MID(A2,FIND("^^",SUBSTITUTE(A2," ","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,1024))
    > >>
    > >> --
    > >> Regards Ron de Bruin
    > >> http://www.rondebruin.nl
    > >>
    > >>
    > >> "Barb" <Barb@discussions.microsoft.com> wrote in message news:16C561D5-99D0-4B77-B813-ADD7413D38CF@microsoft.com...
    > >> >I want to extract a person's last name from a field & put it into another for
    > >> > sorting purposes. If their name is Bud Line, I want to put Line into a
    > >> > different field. If their name is Betsy Sue Color, I want to put Color into
    > >> > a different field. I think their should be a way to search for the space
    > >> > starting from the right side, but I don't know how. The problem I'm having
    > >> > is if their name is listed as first, middle and last.
    > >>
    > >>
    > >>

    >
    >
    >


  7. #7
    Barb
    Guest

    Re: Extract last name and insert into a different field.

    The only problem with this way is that some of the people have 3 names (ex.
    Billy Jo Jones) and some only have two (Bobby Jones). Therefore, not all the
    last names end up in the same column. Thanks for responding.

    "wjohnson" wrote:

    >
    > Work with a copy before doing this:
    > Select the column and then on the menu bar select REPLACE:
    > In the FIND box - use the space bar and enter 1 space
    > In the REPLACE box - enter a comma.
    > Then do a replace all
    > This should replace all space between the names with a comma.
    > Now select the column and then go to the MENU Bar and Select DATA -
    > TEXT TO COLUMNS and follow the instructions.
    >
    >
    > --
    > wjohnson
    > ------------------------------------------------------------------------
    > wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640
    > View this thread: http://www.excelforum.com/showthread...hreadid=517485
    >
    >


  8. #8
    Ron de Bruin
    Guest

    Re: Extract last name and insert into a different field.

    Hi v

    Try it on a new sheet and you see that this is not the problem of the function

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


    "Barb" <Barb@discussions.microsoft.com> wrote in message news:AEE97CD7-CADB-4151-A95D-75DA85D99309@microsoft.com...
    > If I put the formula in B1, the last name appears in B1 but C1 becomes a
    > merged cell with B1 and both cells have a dark grey background.
    >
    > "Ron de Bruin" wrote:
    >
    >> > Works great except it uses up two lines for the answer & it has a grey
    >> > background.

    >>
    >> I do not understand you ?
    >>
    >>
    >>
    >> --
    >> Regards Ron de Bruin
    >> http://www.rondebruin.nl
    >>
    >>
    >> "Barb" <Barb@discussions.microsoft.com> wrote in message news:9E773E68-DCD1-409B-9858-8E5AA3C0115A@microsoft.com...
    >> > Works great except it uses up two lines for the answer & it has a grey
    >> > background.
    >> >
    >> > "Ron de Bruin" wrote:
    >> >
    >> >> Hi Barb
    >> >>
    >> >> Try this formula with the name in A2
    >> >>
    >> >> =IF(ISERR(FIND(" ",A2)),A2,MID(A2,FIND("^^",SUBSTITUTE(A2," ","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,1024))
    >> >>
    >> >> --
    >> >> Regards Ron de Bruin
    >> >> http://www.rondebruin.nl
    >> >>
    >> >>
    >> >> "Barb" <Barb@discussions.microsoft.com> wrote in message news:16C561D5-99D0-4B77-B813-ADD7413D38CF@microsoft.com...
    >> >> >I want to extract a person's last name from a field & put it into another for
    >> >> > sorting purposes. If their name is Bud Line, I want to put Line into a
    >> >> > different field. If their name is Betsy Sue Color, I want to put Color into
    >> >> > a different field. I think their should be a way to search for the space
    >> >> > starting from the right side, but I don't know how. The problem I'm having
    >> >> > is if their name is listed as first, middle and last.
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>




  9. #9
    Barb
    Guest

    Re: Extract last name and insert into a different field.

    THANK YOU! I DON'T KNOW WHAT I DID BEFORE. PERFECT!

    "Ron de Bruin" wrote:

    > Hi v
    >
    > Try it on a new sheet and you see that this is not the problem of the function
    >
    > --
    > Regards Ron de Bruin
    > http://www.rondebruin.nl
    >
    >
    > "Barb" <Barb@discussions.microsoft.com> wrote in message news:AEE97CD7-CADB-4151-A95D-75DA85D99309@microsoft.com...
    > > If I put the formula in B1, the last name appears in B1 but C1 becomes a
    > > merged cell with B1 and both cells have a dark grey background.
    > >
    > > "Ron de Bruin" wrote:
    > >
    > >> > Works great except it uses up two lines for the answer & it has a grey
    > >> > background.
    > >>
    > >> I do not understand you ?
    > >>
    > >>
    > >>
    > >> --
    > >> Regards Ron de Bruin
    > >> http://www.rondebruin.nl
    > >>
    > >>
    > >> "Barb" <Barb@discussions.microsoft.com> wrote in message news:9E773E68-DCD1-409B-9858-8E5AA3C0115A@microsoft.com...
    > >> > Works great except it uses up two lines for the answer & it has a grey
    > >> > background.
    > >> >
    > >> > "Ron de Bruin" wrote:
    > >> >
    > >> >> Hi Barb
    > >> >>
    > >> >> Try this formula with the name in A2
    > >> >>
    > >> >> =IF(ISERR(FIND(" ",A2)),A2,MID(A2,FIND("^^",SUBSTITUTE(A2," ","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,1024))
    > >> >>
    > >> >> --
    > >> >> Regards Ron de Bruin
    > >> >> http://www.rondebruin.nl
    > >> >>
    > >> >>
    > >> >> "Barb" <Barb@discussions.microsoft.com> wrote in message news:16C561D5-99D0-4B77-B813-ADD7413D38CF@microsoft.com...
    > >> >> >I want to extract a person's last name from a field & put it into another for
    > >> >> > sorting purposes. If their name is Bud Line, I want to put Line into a
    > >> >> > different field. If their name is Betsy Sue Color, I want to put Color into
    > >> >> > a different field. I think their should be a way to search for the space
    > >> >> > starting from the right side, but I don't know how. The problem I'm having
    > >> >> > is if their name is listed as first, middle and last.
    > >> >>
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1