need help with a formula to separate a list of names into first, middle initials and last

[AVFoster]

i am having some trouble getting the result i need...i have a list of names first,middle initial(s) and last as shown below..

Marley I. N. Alleyne

Alissa G. Baptiste-Pierre

Olianna S. Bishop

Russell S. M. Butcher

Devon P. Bynoe

<tbody>
</tbody>

all of the formulas i try seem to work fine when there is only one middle initial but the instance where there are two the results are off.
would appreciate any help.

 

[AkaTrouble]

with your full name in A2

in B2

=LEFT(A2,FIND(" ",A2))

in C2

=MID(A2,LEN(B2)+1,(LEN(A2))-(LEN(B2)+LEN(D2)))

in D2

=TRIM(RIGHT(SUBSTITUTE(A2,".",REPT(" ",LEN(A2))),LEN(A2)))

 

[Scott T]

	A	B	C	D
1		First Name	MI	Last Name
2	Marley I. N. Alleyne	Marley	I.N.	Alleyne
3	Alissa G. Baptiste-Pierre	Alissa	G.	Baptiste-Pierre
4	Olianna S. Bishop	Olianna	S.	Bishop
5	Russell S. M. Butcher	Russell	S.M.	Butcher
6	Devon P. Bynoe	Devon	P.	Bynoe

<tbody>
</tbody>

If the middle initial always has a period and never more then two as in your examples then this should work
In B2 and copy down

=TRIM(LEFT(A2,SEARCH(".",A2)-2))

In C2 and copy down

=TRIM(IF(ISERROR(LEFT(MID(A2,SEARCH(".",A2)-1,SEARCH(".",A2,SEARCH(".",A2)+1)),LEN(D2)-1)),MID(A2,SEARCH(".",A2)-1,2),LEFT(MID(A2,SEARCH(".",A2)-1,SEARCH(".",A2,SEARCH(".",A2)+1)),LEN(D2)-1)))

In D2 and copy down

=IF(ISERROR(TRIM(MID(A2,SEARCH(".",A2,SEARCH(".",A2)+1)+1,99))),TRIM(MID(A2,SEARCH(".",A2)+1,99)),TRIM(MID(A2,SEARCH(".",A2,SEARCH(".",A2)+1)+1,99)))

 

[AkaTrouble]

just update to my C2 (middle names) code to make only using full name (A2) as source data

=MID(A2,LEN(LEFT(A2,FIND(" ",A2)))+1,(LEN(A2))-(LEN(LEFT(A2,FIND(" ",A2)))+LEN(TRIM(RIGHT(SUBSTITUTE(A2,".",REPT(" ",LEN(A2))),LEN(A2))))))

 

[AVFoster]

just update to my C2 (middle names) code to make only using full name (A2) as source data

=MID(A2,LEN(LEFT(A2,FIND(" ",A2)))+1,(LEN(A2))-(LEN(LEFT(A2,FIND(" ",A2)))+LEN(TRIM(RIGHT(SUBSTITUTE(A2,".",REPT(" ",LEN(A2))),LEN(A2))))))

Thanks Mate!
This update works brilliantly! It has even correctly identified middle initials where they were 3 or 4 letters (didn't know they were in the list).
Much appreciated.

 

[AkaTrouble]

Thanks Mate!
This update works brilliantly! It has even correctly identified middle initials where they were 3 or 4 letters (didn't know they were in the list).
Much appreciated.

your welcome it was Scott T

that highlighted my using result rather than original source with his code hence update

 

[AVFoster]

my results using this solution weren't as consistent:

Full Name	First	Middle	Last
Christian A. B. Dalrymple	Christian	A. B. Da	Dalrymple
Trevon M. L. Holdip-Kellman	Trevon	M. L. Holdip	Holdip-Kellman
Micarla R. B. P. Jemmott	Micarla	R. B. P.	P. Jemmott
Jayden A. E. Phillips	Jayden	A. E. P	Phillips

<tbody>
</tbody>

can you tweak?

 

[AkaTrouble]

i get correct results using formula

Christian A. B. Dalrymple	Christian	A. B.	Dalrymple
Trevon M. L. Holdip-Kellman	Trevon	M. L.	Holdip-Kellman
Micarla R. B. P. Jemmott	Micarla	R. B. P.	Jemmott
Jayden A. E. Phillips	Jayden	A. E.	Phillips

<tbody>
</tbody>

Christian A. B. Dalrymple	=LEFT(A1,FIND(" ",A1))	=MID(A1,LEN(LEFT(A1,FIND(" ",A1)))+1,(LEN(A1))-(LEN(LEFT(A1,FIND(" ",A1)))+LEN(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",LEN(A1))),LEN(A1))))))	=TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",LEN(A1))),LEN(A1)))
Trevon M. L. Holdip-Kellman	=LEFT(A2,FIND(" ",A2))	=MID(A2,LEN(LEFT(A2,FIND(" ",A2)))+1,(LEN(A2))-(LEN(LEFT(A2,FIND(" ",A2)))+LEN(TRIM(RIGHT(SUBSTITUTE(A2,".",REPT(" ",LEN(A2))),LEN(A2))))))	=TRIM(RIGHT(SUBSTITUTE(A2,".",REPT(" ",LEN(A2))),LEN(A2)))
Micarla R. B. P. Jemmott	=LEFT(A3,FIND(" ",A3))	=MID(A3,LEN(LEFT(A3,FIND(" ",A3)))+1,(LEN(A3))-(LEN(LEFT(A3,FIND(" ",A3)))+LEN(TRIM(RIGHT(SUBSTITUTE(A3,".",REPT(" ",LEN(A3))),LEN(A3))))))	=TRIM(RIGHT(SUBSTITUTE(A3,".",REPT(" ",LEN(A3))),LEN(A3)))
Jayden A. E. Phillips	=LEFT(A4,FIND(" ",A4))	=MID(A4,LEN(LEFT(A4,FIND(" ",A4)))+1,(LEN(A4))-(LEN(LEFT(A4,FIND(" ",A4)))+LEN(TRIM(RIGHT(SUBSTITUTE(A4,".",REPT(" ",LEN(A4))),LEN(A4))))))	=TRIM(RIGHT(SUBSTITUTE(A4,".",REPT(" ",LEN(A4))),LEN(A4)))

<tbody>
</tbody>

in A1 full name
in B1 =LEFT(A1,FIND(" ",A1))
in C1 =MID(A1,LEN(LEFT(A1,FIND(" ",A1)))+1,(LEN(A1))-(LEN(LEFT(A1,FIND(" ",A1)))+LEN(TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",LEN(A1))),LEN(A1))))))
in D1 =TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",LEN(A1))),LEN(A1)))

 

[AVFoster]

apologies that 2nd response wasn't for you...your formulae work perfectly every time and in every scenario

 

[FlameRetired]

Another way.

This is an array formula entered in B2 then filled down and across until you get blanks. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A2," ",REPT(" ",256),COUNT(IF(MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)=" ",ROW(INDIRECT("1:"&LEN($A2))))))," ",REPT(" ",256),1),(COLUMNS($B:B)-1)*256+1,256))

Full Name​	First​	Middle​	Last​
Christian A. B. Dalrymple​	Christian​	A. B.​	Dalrymple​
Trevon M. L. Holdip-Kellman​	Trevon​	M. L.​	Holdip-Kellman​
Micarla R. B. P. Jemmott​	Micarla​	R. B. P.​	Jemmott​
Jayden A. E. Phillips​	Jayden​	A. E.​	Phillips​