Seperation of last name from list

I am having a problem separating the last name in attached list. The problem is with names as shown such as 'Robert De Niro', I want to show the last name as 'De Niro' and not 'Niro'.

I think the file will explain this better.

Thanks
Jim O

I'm thinking this will be nearly impossible to achieve with formulas.

There are too many possibilities and no way to define the logic to account for those possibilities.

What about people with 2 word first names:

Billy Bob Thornton
Edward Van Halen

How can you tell Excel that Billy Bob is a first name while Van Halen is the last name?

Good luck!

Try this formula

Formula:

Please Login or Register  to view this content.

! It may not work for all strings.

Good deal. Thanks for the feedback!