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?