Split variety of full names into prefix, first name, middle name, last name and suffix using a formula

[VictoriaExcel]

Hi,

I have a list of thousands of names that need splitting into columns: prefix, first name, middle name, last name and suffix. The full names are varied and therefore the formulas I have tried thus far do not work on the whole list.

Formulas I have tried thus far:

Prefix
=LOOKUP(9.99E+307,SEARCH(Prefix,E7),Prefix)
To look up against a table of possible Prefix (downside being that I may not have captured all possibilities in this table)

First Name
=LEFT(G3,SEARCH(" ",G3))
Doesn't work if the name contains more than one prefix e.g. Professor Doctor Stephen Phillip DUNN

Middle Name
=MID(G3,SEARCH(" ",G3,1)+1,SEARCH(" ",G3,SEARCH(" ",G3,1)+1)-SEARCH(" ",G3,1))
This works in the main

Last Name
=RIGHT(G3,LEN(G3)-SEARCH("#",SUBSTITUTE(G3," ","#",LEN(G3)-LEN(SUBSTITUTE(G3," ","")))))
This picks up suffixes e.g. CBE, DL or last name not one or the other

Suffix
Same issue as prefix and last name

Example names:
[TABLE="width: 341"]
<colgroup><col></colgroup><tbody>[TR]
[TD][TABLE="width: 341"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Richard John William ALSTON CBE
William Blackledge BEAUMONT CBE DL
Ian Craig BLATCHFORD
Donald Hood BRYDON CBE
Rt Hon Alan CAMPBELL MP
Alastair Nathan COOK CBE
Professor Jeremy James FARRAR OBE
Professor Michael Anthony John FERGUSON CBE FRS
Professor Melvyn Francis GREAVES
Professor Alexander Norman HALLIDAY FRS
Professor David KLENERMAN FRS
James Henry LEIGH-PEMBERTON CVO
John Henry James LEWIS OBE
James MACKEY
Professor Jonathan Robert MONTGOMERY
Philip Nicholas Outram PULLMAN CBE
Rt Hon John Alan REDWOOD MP
Roy Alexander STONE CBE
Gary Nicholas STREETER MP
Dr Patrick John Thompson VALLANCE
Richard HEATON CB
Jonathan Michael THOMPSON
Gillian Elizabeth AITKEN
Myfanwy BARRETT
Julie GILLIS
Patricia Jane HAYES
Dr James Colin RICHARDSON
Neil THOMPSON
Peter Derek WATKINS CBE
Richard Ian WEST
Professor Madeleine Julia ATKINS CBE
Jayne-Anne GADHIA CBE
Ann Heron GLOAG OBE
Marianne GRIFFITHS
Lesley (Twiggy) LAWSON
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance! Any suggestions welcome.

Best wishes,

Victoria

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Yes, this is a classic problem when it comes to names. There are so many different variations with names, it is virtually impossible to come up with any sort of rule that will always work.

Forget about the formulas and programming side of things for a moment. Can you just explain, in plain English, the exact rules?
If you cannot explain it in plain English, then you probably cannot come up with a formula to do it.
(I often tell people Excel is not magic, it is very literal. Like any other computer programming, it can only do exactly what we instruct it to do).

When it comes to problems like this, usually the best you can do is to come up with formulas that work in MOST cases, but then realize that you are still going to have to do some manual verification and clean-up work afterwards.