Names to Columns; incl (Honorific) Title, First Name, Middle Name(s), Last Name and Suffix

I recently (June 2025) responded to a thread from Len Silva asking for a way to separate names to columns, including (Honorific) Title, First Name, Middle Name(s), Last Name and Suffix. In fact, pretty much any combination, some or all of the above. (See link below).

In the past, I'd have thought it was impossible to answer this with a formula, even one copied across and down. I would have resorted to VBA and, in fact, the OP was asking for/expecting a VBA solution.

However, with Excel 365, I found it was/is possible to provide a single SPILLed formula taking into account all the OP's requirements (I think).

Anyway, having provided that solution, I got to thinking that it could be improved on and make it easier to cope with more Titles and Suffices. To that end, I added a reference sheet where these parameters could be added or removed easily without having to "play" with the SPILL formula (with potentially disastrous consequences).

The attached workbook contains the various stages of the development of the "final" solution. I have tried to test different combinations and I am aware of some limitations around single names, with or without titles, and Regnal Numbers (I, IV, V, VI). Hopefully, these are not show stoppers.

As ever, any advice, comments and suggestions would be most welcome. I hope it proves useful. Enjoy!

Original thread that prompted this Tip/Tutorial:
Names to Columns

I do volunteer work for non profits and faced with about 2000 names, each in one cell. I know all the normal solutions, Text to Columns, Left (Find) etc., but what do you do with two (or more) first name words or last name name words? What do you do with "John Van Dyke" or "Mary Ann Jones" or worse, "Mary Ann Van Dyke"? These are wealthy donors with honorifics; Professor, The Honorable, etc.. I could probably list all the multi word titles and suffixes and use MATCH, but how to deal with two (or more) word names?
I'm sure many have faced this issue, looking for ideas,
Thanks

Len Silva?*
Forum Contributor
Join Date; 11/03/2009
Location; Florida
MS-Off Ver: Excel 365

Hey Trevor,

Trying to do this problem on a large genealogy file a few years ago. Names/Prefix like "Prince","King","Captain" or "Judge" were spoilers for me. They could be used as a first name or a title.

Also there were always the different ways to abbreviate titles like: Jr, Jr., Junior which was hard to pull out, but your system could enter them separately. Then military abbreviations like: Lt. Col. which were hard to separate.

I finally realized it would never be perfect for all datasets.

@MarvinP: Absolutely!

As for the options for the spelling of the titles and suffices, that was the reason for creating the reference lists on a separate sheet and collating them.

Even then, there are pitfalls - you have to put Mrs before Mr or the Regex will pick Mr for both/either.

Fixing the issue with Van whatever was a bonus