Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Extract Full Names - Including Titles and Suffixes

0

I need some help. How can I extract a name that included an honorific prefix, having two first names and a suffix? Here is an example:

  • A1 = Mr. Stanley Christopher Lyons Tower Cornell Cook IV
  • B1 = Mr.
  • C1 = Stanley Christopher
  • D1 = Lyons Tower Cornell
  • E1 = Cook
  • F1 = IV

Is there a way for which I can split all five of these components? If there is not one of any, I could just leave it blank. Sometimes, the honorific prefix could just spell out in full (Pastor, Monsieur, Mister) and sometimes an abbreviated one (Dr., Engr. Msgr.) while in some cases, there have been professional suffixes like Ph. D., M.D., QC, and so forth.

excelformulas-functionsnames
posted by: JCEurovisionFan on Tue Jul 19, 2022 at 10:31 am
Answer
Discuss

Discussion

Hi JC and welcome to the Forum.

Excel has worksheet functions for string (text) manipulation to do this kind of thing - see the Tutorials page where there's a section on it. You could use Don's tutorial file Extract Text Before or After the 1st, 2nd, 3rd, Nth Delimiter in Excel to extract every word in a name string, for example. 

The problem is that you're trying to extract elements which are ill-defined (e.g. which is the last of the first names? Is Bruce a first name or a Scots surname?)

Can you list all the expected honorific prefixes (and abbreviations) and professional suffixes? What's your data source (and can you be sure all entries use correct spellings of the above)?
John_Ru (rep: 6142) Jul 19, '22 at 11:29 am
I am talking about suffixes such as Jr., Sr., III, IV, Esq., Ph. D., MBA, the Bruce, the Great, the Magnificent, MP, MSP, MEP, QC, etc., and prefixes such as Mr., Ms., Mrs., Dame, Sir, The Honorable, President, Professor, Lieutenant, Lance Corporal, etc.
JCEurovisionFan Jul 19, '22 at 12:46 pm
I realise that but asked you if you could list every one which might be encountered from your source. If not, it's going to be impossible to extract them. 
John_Ru (rep: 6142) Jul 19, '22 at 4:43 pm
I have a draft answer but assume (from your lack of comment) that you've given up or found an answer elsewhere.
John_Ru (rep: 6142) Jul 26, '22 at 4:35 am
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login