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

[KimprintCreative]

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.

 

[KimprintCreative]

... better formula for column B ..

Split Names (2)

*	A	B	C	D	E	F
1	Rev. Kimberly E. Holmes, DMin	Rev.	Kimberly	E.	Holmes	DMin
2	Reverend Kimberly E. Holmes, DMin	Reverend	Kimberly	E.	Holmes	DMin
3	Kimberly E. Holmes, DMin	*	Kimberly	E.	Holmes	DMin
4	Kimberly Holmes, DMin	*	Kimberly	*	Holmes	DMin
5	Kimberly Holmes	*	Kimberly	*	Holmes	*
6	Kimberly Holmes, Dmin	*	Kimberly	*	Holmes	Dmin
7	Rev Kimberly Holmes, DMin	Rev	Kimberly	*	Holmes	DMin
8	Rev. Kimberly E. Holmes	Rev.	Kimberly	E.	Holmes	*
9	Minister Kimberly Holmes, Dmin	Minister	Kimberly	*	Holmes	Dmin
10	Min. Kimberly Holmes, Dmin	Min.	Kimberly	*	Holmes	Dmin
11	Min. Kimberly X. Holmes	Min.	Kimberly	X.	Holmes	*
12	Pastor Kimberly Z. Holmes, Dmax	Pastor	Kimberly	Z.	Holmes	Dmax

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+(D1="")+(F1="")=4,LEFT(A1,FIND(" ",A1)-1),"") C1 =TRIM(LEFT(SUBSTITUTE(A1,B1,"",1),FIND(" ",SUBSTITUTE(A1,B1,"",1),2))) D1 =MID(A1,SEARCH(" ?. ",A1&" .. ")+1,2) E1 =MID(SUBSTITUTE(A1,", "&F1,""),LEN(B1&C1&D1)+COUNTIF(B1:D1,"?*")+1,LEN(A1)) F1 =REPLACE(A1,1,FIND(",",A1&",")+1,"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you! I took some of the formatting I had out in the sheet to simplify things and now it is working for me. Now the challenge is getting it to copy to all the cells. For some reason, when I copy the cells it sets the reference to two cells above.

Does anyone know how I can mark this as solved?

 

[Peter_SSs]

Thank you! I took some of the formatting I had out in the sheet to simplify things and now it is working for me. Now the challenge is getting it to copy to all the cells. For some reason, when I copy the cells it sets the reference to two cells above.

If you put those formulas in row 1 and copy them to other rows they should be fine.

If you put those formulas in row 3 and then copy them, they will all refer to two rows above themselves.

Does anyone know how I can mark this as solved?

Your sentence above has done it!
We don't do any more than that in this forum as somebody else might still come along with a better answer.