Text formula for names in 2 column Last and First Name

[excelaron]

Hello, I'm having problem with names in a column A for example

I need to break up the name into last name - column A and first name in column B

Smith John
Jones Brown Randy

I need the Name in 2 columns

column A - Smith column B - John

column A - Jones Brown column B - Randy

So the logic is if 3 names strings in column A the first 2 strings = Last name and the last name string = first name
if on 2 name strings in column A then only first and last name (this I can do) but it's the 3 name strings in 1 column that is the problem.



Thanks for any suggestion or where to point me to an example

[Moo the Dog]

There are likely easier ways to do this, but what the heck...

If your list of names is in column E (E1 and down), put the first formula in A1, and the second formula in B1, and fill both down:

A1

Please Login or Register  to view this content.

B1

Please Login or Register  to view this content.

-Moo

[Pauleyb]

This is technique I have seen before.

To find the last name (let's say it is in C1):
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))

Then to find the first name(s) (in B1):
=LEFT(A1,LEN(A1)-LEN(C1))

[excelaron]

Thanks it works