Split up first and last name

[hambone]

I have a list of names. It is always in the format of:

FirstName LastName

I want to create a column that returns just the last name. Where it gets tricky is when some names are like this:

FirstName LastName Jr.
FirstName LastName III

In those instances, I don't want the Jr. or the III, I still want just the last name.

Any help?

I've been using the formula =RIGHT(A1,LEN(A1)-FIND(" ",A1))
But that doesn't work when the last name is followed by a Jr.

Thanks!

[JBeaucaire]

How about:

=TRIM(SUBSTITUTE(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND(" ",A1)),"Jr.",""),"III",""))

[Armando Montes]

Another one:
=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)

[Trebor76]

Since the first name and last name are always separated by the same deliminter, you can use this:

=MID(A1,SEARCH(" ",A1)+1,LEN(A1)-LEN(SEARCH(" ",A1)))

[DonkeyOte]

Assuming you have no spaces amid first name references you should find something like the below will work irrespective of suffix

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),1+LEN(A1),LEN(A1)))

above assumes per first post that you're looking to strip the suffix should it exist