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

VictoriaExcel

New Member
Joined
Nov 15, 2018
Messages
14
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:
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

<colgroup><col></colgroup><tbody>
</tbody>

Thank you in advance! Any suggestions welcome.

Best wishes,

Victoria


<colgroup><col></colgroup><tbody>
</tbody>


 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,909
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,269
Messages
5,635,193
Members
416,846
Latest member
ImGoing2needaFormula

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top