Extract last name and insert into a different field.

[Guest]

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.

 

[Ron de Bruin]

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))

 

[wjohnson]

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.

 

[Guest]

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

 

[Ron de Bruin]

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

background.

I do not understand you ?

 

[Guest]

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.

 

[Guest]

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.

 

[Ron de Bruin]

Hi v

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

 

[Guest]

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