Split Full Name--Including Title & Suffix--Using a Formula

KimprintCreative

New Member
Joined
Sep 25, 2014
Messages
5
Hello! So, I have a spreadsheet with the title, first name, middle initial, last name, and suffix in one cell. Using a formula, is there a way I can split all of this and, if a cell does not have all four components, just have it fill in what it does have?

So...

A1 = Rev. Kimberly E. Holmes, DMin
B1 = Rev.
C1 = Kimberly
D1 = E.
E1 = Holmes
F1 = DMin

Sometimes the title will be a full one (Pastor, Minister, etc) and sometimes an abbreviated one (Dr., Atty,. etc).

Thank you in advance to anyone reading this who can/will offer help and thank you to the mods in case I have posted this in the wrong place or am asking a question that has already been answered. :LOL:
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Without creating a very extensive table of every title and suffix possible I don't believe this would be possible (at least not in any reliable way). Without such an extensive lookup table, there would be no programmatic way for a formula to distinguish the parts of a block of text.
 
Upvote 0
Without creating a very extensive table of every title and suffix possible I don't believe this would be possible (at least not in any reliable way). Without such an extensive lookup table, there would be no programmatic way for a formula to distinguish the parts of a block of text.

Thank you for that answer. Your reply helped me to clarify more of my situation. In this case, there would be six possible titles and seven possible suffix options. I was thinking there would be a way for a formula to distinguish the spaces or punctuation, as opposed to the actual words, though. For example, I have seen (and used) formulas that find a hyphenated last name and place it into the last name column.
 
Upvote 0
As excel can’t make difference between Minister and Kimberly etc., titles, if there exist, must have „.” at the end. Therefore you should replace every Minister with Min., for example, and do this with all titles which do not end with a period. Then try to use the formulas below:

B1:

=IF(MID(A1,FIND(" ",A1)-1,1)=".",LEFT(A1,FIND(" ",A1)-1),"")

C1:

=TRIM(LEFT(TRIM(SUBSTITUTE($A1,$B1,"")),FIND(" ",TRIM(SUBSTITUTE($A1,$B1,"")))))

D1:

=IFERROR(MID(A1,SEARCH(" ?. ",A1)+1,2),"")

E1:

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE($A1,$F1,""))," ",REPT(" ","99")),99)),",","")

F1:

=IFERROR(RIGHT($A1,LEN(A1)-FIND(",",$A1)-1),"")
 
Upvote 0
As excel can’t make difference between Minister and Kimberly etc., titles, if there exist, must have „.” at the end. Therefore you should replace every Minister with Min., for example, and do this with all titles which do not end with a period. Then try to use the formulas below:

B1:

=IF(MID(A1,FIND(" ",A1)-1,1)=".",LEFT(A1,FIND(" ",A1)-1),"")

C1:

=TRIM(LEFT(TRIM(SUBSTITUTE($A1,$B1,"")),FIND(" ",TRIM(SUBSTITUTE($A1,$B1,"")))))

D1:

=IFERROR(MID(A1,SEARCH(" ?. ",A1)+1,2),"")

E1:

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE($A1,$F1,""))," ",REPT(" ","99")),99)),",","")

F1:

=IFERROR(RIGHT($A1,LEN(A1)-FIND(",",$A1)-1),"")


Thanks! This was a good help. The formulas listed for C1 & E1 won't work for me, but it's much better than where I was.
 
Upvote 0
Thanks! This was a good help. The formulas listed for C1 & E1 won't work for me, but it's much better than where I was.

It is worth trying to find the difference, as it works for me as requested. See the spreadsheet below:

B1:
=IF(MID(A1,FIND(" ",A1)-1,1)=".",LEFT(A1,FIND(" ",A1)-1),"")
C1:
=TRIM(LEFT(TRIM(SUBSTITUTE($A1,$B1,"")),FIND(" ",TRIM(SUBSTITUTE($A1,$B1,"")))))
D1:
=IFERROR(MID(A1,SEARCH(" ?. ",A1)+1,2),"")
E1:
=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE($A1,$F1,""))," ",REPT(" ","99")),99)),",","")
F1:
=IFERROR(RIGHT($A1,LEN(A1)-FIND(",",$A1)-1),"")
Excel Workbook
ABCDEF
1Rev. Kimberly E. Holmes, DMinRev.KimberlyE.HolmesDMin
2Kimberly E. Holmes, DMinKimberlyE.HolmesDMin
3Kimberly Holmes, DMinKimberlyHolmesDMin
4Kimberly HolmesKimberlyHolmes
5Kimberly Holmes, DminKimberlyHolmesDmin
6Rev. Kimberly Holmes, DMinRev.KimberlyHolmesDMin
7Rev. Kimberly E. HolmesRev.KimberlyE.Holmes
8Minister. Kimberly Holmes, DminMinister.KimberlyHolmesDmin
9Min. Kimberly Holmes, DminMin.KimberlyHolmesDmin
10Min. Kimberly X. HolmesMin.KimberlyX.Holmes
11Pars. Kimberly Z. Holmes, DmaxPars.KimberlyZ.HolmesDmax
Sheet
 
Upvote 0
Here are my attempts. The assumption is that each line will have two "full" names with optional title, optional middle initial (with "."), optional suffix (following ", ").

This does not require that titles are followed by a period, but still works if that is the case.

Excel Workbook
ABCDEF
1Rev. Kimberly E. Holmes, DMinRev.KimberlyE.HolmesDMin
2Reverend Kimberly E. Holmes, DMinReverendKimberlyE.HolmesDMin
3Kimberly E. Holmes, DMinKimberlyE.HolmesDMin
4Kimberly Holmes, DMinKimberlyHolmesDMin
5Kimberly HolmesKimberlyHolmes
6Kimberly Holmes, DminKimberlyHolmesDmin
7Rev Kimberly Holmes, DMinRevKimberlyHolmesDMin
8Rev. Kimberly E. HolmesRev.KimberlyE.Holmes
9Minister Kimberly Holmes, DminMinisterKimberlyHolmesDmin
10Min. Kimberly Holmes, DminMin.KimberlyHolmesDmin
11Min. Kimberly X. HolmesMin.KimberlyX.Holmes
12Pastor Kimberly Z. Holmes, DmaxPastorKimberlyZ.HolmesDmax
Split Names
 
Upvote 0
... better formula for column B ..

Excel Workbook
ABCDEF
1Rev. Kimberly E. Holmes, DMinRev.KimberlyE.HolmesDMin
2Reverend Kimberly E. Holmes, DMinReverendKimberlyE.HolmesDMin
3Kimberly E. Holmes, DMinKimberlyE.HolmesDMin
4Kimberly Holmes, DMinKimberlyHolmesDMin
5Kimberly HolmesKimberlyHolmes
6Kimberly Holmes, DminKimberlyHolmesDmin
7Rev Kimberly Holmes, DMinRevKimberlyHolmesDMin
8Rev. Kimberly E. HolmesRev.KimberlyE.Holmes
9Minister Kimberly Holmes, DminMinisterKimberlyHolmesDmin
10Min. Kimberly Holmes, DminMin.KimberlyHolmesDmin
11Min. Kimberly X. HolmesMin.KimberlyX.Holmes
12Pastor Kimberly Z. Holmes, DmaxPastorKimberlyZ.HolmesDmax
Split Names (2)
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

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
Back
Top