I have a strange situation that I am trying to resolve.

I have a list of names in a different format.All I want to do is add a period to a single letter since it's a middle initial.

I tried:

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

This works, but will miss entities with "&" and another name.So in A2, the result is Michael F.& Jane J instead of Michael F.& Jane J.

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

In a cell with a single name and no middle initials or cells without a name or a filled middle name case, this will encounter an error.

Any suggestions? Text to columns also works.I could separate them and run the above formula on all four columns, then join, but that's an inefficient procedure.

Thank you!

uj5u.com enthusiastic netizens replied:

inB2, copy the formula:

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

Edit:

If your source has spaces after the last paragraph, try adding a trim() to remove the last space , the formula becomes >>

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

uj5u.com enthusiastic netizens replied:

is a bit far-fetched, but with Excel Microsoft365, use LET ():

FormulaB2:

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