Splitting first, middle initial, and last name

[eb818]

I have looked everywhere for an answer on this so I am hoping someone will be able to help me out here!

If I have names entered in my A column such as:

Joe Johnson
Calvin Bryant IV
Matt K. Smith
John P. Doe Jr.


How can I break them up so that the first name, middle initial, and last name with the suffix go in three separate columns? Is this something that would need a VBA code? When looking at other people's answers, their formulas never worked for what my issue is.

Thanks!

[p45cal]

You can 99% of the way. Take a name like your Calvin Bryant IV, to us humans we're pretty certain this is first name Colin, lastname Bryant with suffix IV. We could write some code that when letters like IV appear at the end that it's taken to be roman numerals and interpreted as a lastname suffix. But what if you have a name such as Lee Hung XI? Is it Lee Hung the eleventh, or is the last name Xi?

Are all the names like your examples or are there further variations such as:
Matt J. W. Johnson
Harrison K
Mr. Obama
Doe John

This can get quite involved, so could you supply a bigger sample of a few hundred names (you can do a search and replace first if these names are in any way sensitive)?
Also, re:"I have looked everywhere for an answer on this", try a Google with:
excel vba split first name last name
It may come up with something useful.

[mancubus]

below thread may give an idea too.

not same but similar.

http://www.vbaexpress.com/forum/showthread.php?48883-Extract-1st-4-letters-of-last-name-ideas

[eb818]

Figured it out! Thanks guys!