Extract last name and insert into a different field.

B

Barb

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.
 
R

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

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.
 
B

Barb

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.
 
B

Barb

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.
 
R

Ron de Bruin

Hi v

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