On Thu, 1 Nov 2007 08:45:05 -0700, Eric wrote:

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