Adding Period to Single Letters, i.e., middle initial (If single letter, add period, if not leave no-CodePudding

I have a weird situation I am trying to work through.

I have a list of names in different formats. All I want to do is add a period to single letters because it is a middle initial.

I have tried:

=IF(LEN(A2)=1,A2&". ",IF(A2="","",A2&" "))

This works but misses instances with '&' and another name. So in A2, the result comes out to Michael F. & Jane J instead of Michael F. & Jane J.

Any suggestions? Text to columns also works. I can split them out and then run the above formula on all four columns then concatenate but it is an inefficient process.

Thank you!

CodePudding user response：

In B2, formula copied down :

=SUBSTITUTE(SUBSTITUTE(A2," &",". &")&".","..",".")

Edit :

If you source data have a space after the last period, try to add a trim() to remove the last space and the formula become >>

=SUBSTITUTE(SUBSTITUTE(TRIM(A2)," &",". &")&".","..",".")

CodePudding user response：

Bit of a stretch, but with Excel Microsoft365, using LET():

Formula in B2:

=LET(X,FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A2,"&","&#38;")," ","</s><s>")&"</s></t>","//s"),TEXTJOIN(" ",,IF(LEN(X)=1,IF(X<>"&",X&".",X),X)))