Reformat name

[RattlingCarp3048]

I have a set of names i need to reformat. normally i would just separate out the names into helper columns and concat what i need from there. in this particular case i cannot do that. Sooo i have this formula which is close but not quite. Can someone help get me over the finish line?

C3 >>>A3
Snow, John F >>>John Snow
Snow, John >>>> John Snow
Snow Targaryen, John F >>> John Snow Targaryen

=MID(C3,SEARCH(",",C3)+1,LEN(C3)-SEARCH(",",C3)-1)&" "&LEFT(C3,SEARCH(",",C3)-1)

currently if there is no middle initial it is cutting off the last letter of the first name instead

 

[Fluff]

How about

=LET(s,SEARCH(",",C3),m,TRIM(MID(C3,s+1,100)),LEFT(m,FIND(" ",m&" ")-1)&" "&LEFT(C3,s-1))

 

[RattlingCarp3048]

How about

=LET(s,SEARCH(",",C3),m,TRIM(MID(C3,s+1,100)),LEFT(m,FIND(" ",m&" ")-1)&" "&LEFT(C3,s-1))

that did it! thanks Fluff

 

[Fluff]

You're welcome & thanks for the feedback.