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.
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.
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" <[email protected]> wrote in message news:[email protected]...
>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.
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" <[email protected]> wrote in message news:[email protected]...
> >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.
>
>
>
> 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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> >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.
>>
>>
>>
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" <[email protected]> wrote in message news:[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> >> >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.
> >>
> >>
> >>
>
>
>
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
>
>
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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> > 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" <[email protected]> wrote in message news:[email protected]...
>> >> >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.
>> >>
>> >>
>> >>
>>
>>
>>
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" <[email protected]> wrote in message news:[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> >> > 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" <[email protected]> wrote in message news:[email protected]...
> >> >> >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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks