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.

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" wrote in message ...
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.

Extract last name and insert into a different field.
 

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

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" wrote in message ...
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.

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" wrote in message ...
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" wrote in message ...
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.

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" wrote in message ...
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" wrote in message ...
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.

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

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" wrote in message ...
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" wrote in message ...
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" wrote in message ...
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.

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" wrote in message ...
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" wrote in message ...
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" wrote in message ...
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.