Find last name in multi-part name?

[Guest]

I have a list of people's names. Each name consists of 2-6 individual names,
in order FIRSTNAME MIDDLENAME LASTNAME format, separated by spaces. These
are lists of staff in India, and in some cases there are multiple middle
names: FIRSTNAME MIDDLENAME1 MIDDLENAME2 MIDDLENAME3 LASTNAME.

I need to take apart the names so that I have the last name in one column
and the first name + middle names (if any) in another column.

I'm hoping to find a couple of formulas that would do this.

Alternatively, I could use "convert text to columns", but since the names
have different lengths, I wouldn't always end up with the last name in the
same column. Is there some way to identify the last non-blank cell in a
range?

Suggestions?

 

[JE McGimpsey]

One way:

First name:

=LEFT(A1,FIND(" ",A1)-1)

Last name:

=MID(A1,FIND("$",SUBSTITUTE(A1," ","$",LEN(A1)-LEN(SUBSTITUTE(A1," ",
""))))+1,255)

the latter assumes that there are no last names like "Van Dyke"

 

[Leo Rod]

I see here that you need to separate the name by the spaces in between the
words, for that you may want to use a consecutive series of formulas that
count and tells you the position of the " " in a desired cell e.g. "A1"
unveiling the relative position of it..

This is:
=IF(ISERROR(FIND(" ",$A1,FIND(" ",$A1,FIND(" ",$A1,FIND(" ",$A1,FIND("
",$A1)+1)+1)+1)+1)),FIND(" ",$A1,FIND(" ",$A1,FIND(" ",$A1,FIND("
",$A1)+1)+1)+1),IF(ISERROR(FIND(" ",$A1,FIND(" ",$A1,FIND(" ",$A1,FIND("
",$A1)+1)+1)+1)),FIND(" ",$A1,(FIND(" ",$A1,FIND("
",$A1))+1)+1),IF(ISERROR(FIND(" ",$A1,FIND(" ",$A1)+1)),FIND(" ",$A1))))

the previous paragraph evaluates for five words separated by spaces where
you can't find the " " character and returns the last know position of the "
", on the EG that you provided: "FIRSTNAME MIDDLENAME1 MIDDLENAME2
MIDDLENAME3 LASTNAME" is the # 46.

After that, request the difference of "right" side of that position with the
total length of the text inside the cell and this will return the last word,
assuming that is the "lastname" and not a two or more words composed last
name:

=RIGHT(A1,LEN(A1)-((IF(ISERROR(FIND(" ",$A1,FIND(" ",$A1,FIND(" ",$A1,FIND("
",$A1,FIND(" ",$A1)+1)+1)+1)+1)),FIND(" ",$A1,FIND(" ",$A1,FIND("
",$A1,FIND(" ",$A1)+1)+1)+1),IF(ISERROR(FIND(" ",$A1,FIND(" ",$A1,FIND("
",$A1,FIND(" ",$A1)+1)+1)+1)),FIND(" ",$A1,(FIND(" ",$A1,FIND("
",$A1))+1)+1),IF(ISERROR(FIND(" ",$A1,FIND(" ",$A1)+1)),FIND(" ",$A1)))))))

Some people will find easy solutions, in the meantime try to copy and paste
the above formula.

Respectfully,

Leo Rod.

 

[Guest]

JE -

I've spent half an hour trying to figure this out why this works, and
haven't yet managed.... but I will !

In the meantime, I tried it and it worked fine... except on one name, which
I discovered had a couple of extra blanks on the right. So I'll modify the
formula to TRIM the name before I use it, and it looks like it will work fine.

Thanks for your help!

Eric

 

[Ron Rosenfeld]

I have a list of people's names. Each name consists of 2-6 individual names,
in order FIRSTNAME MIDDLENAME LASTNAME format, separated by spaces. These
are lists of staff in India, and in some cases there are multiple middle
names: FIRSTNAME MIDDLENAME1 MIDDLENAME2 MIDDLENAME3 LASTNAME.

I need to take apart the names so that I have the last name in one column
and the first name + middle names (if any) in another column.

I'm hoping to find a couple of formulas that would do this.

Alternatively, I could use "convert text to columns", but since the names
have different lengths, I wouldn't always end up with the last name in the
same column. Is there some way to identify the last non-blank cell in a
range?

Suggestions?

LASTNAME:

=MID(SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),
FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(
SUBSTITUTE(A1," ",""))))+1,255)

First Name + All Middle Names:

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)
--ron