How do you adjust fomulas for 2nd "space"?

[roger_home]

I have seen this before on this site, and it works great for only 2 names (i.e., John Smith), but as soon as you get a "Mr. John Smith" in A column you get "John Smith" in C column...how do you change formula to move to next space??? Like if some names are 2 words, but some are 3, some 4, some 5, etc. Is there a formula to just pull out last word without using text to columns and then having to merge columns back together?

Isplitting name into firstname and lastname
------------------------------------------------------------------------
i have a list of names in column A. the names are in this format: firstname lastname. i want to write and run a macro which will insert the firstname in column B, and the lastname in column C.

In B1 use:
=LEFT(A1,FIND(" ",A1)-1)

In C1 use:
=RIGHT(A1,LEN(A1)-FIND(" ",A1))

[roger_home]

Still need help

[Domenic]

To extract the last word, try the following...

=MID(A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" "),ROW(INDIRECT("1:"&LEN(A1))))+1,255)

Hope this helps!

I have seen this before on this site, and it works great for only 2 names (i.e., John Smith), but as soon as you get a "Mr. John Smith" in A column you get "John Smith" in C column...how do you change formula to move to next space??? Like if some names are 2 words, but some are 3, some 4, some 5, etc. Is there a formula to just pull out last word without using text to columns and then having to merge columns back together?

Isplitting name into firstname and lastname
------------------------------------------------------------------------
i have a list of names in column A. the names are in this format: firstname lastname. i want to write and run a macro which will insert the firstname in column B, and the lastname in column C.

In B1 use:
=LEFT(A1,FIND(" ",A1)-1)

In C1 use:
=RIGHT(A1,LEN(A1)-FIND(" ",A1))

[roger_home]

Domenic

That worked!

Thanks