| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
