CODEWITHSUNDEEP
excelexcel-formula
Exalted_X
Exalted_X

Reputation: 41

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

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

enter image description here

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.

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

This ran into errors in instances with a single name and no middle initial or when there are no names in the cell or when there is a fill middle name.

enter image description here

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!

Upvotes: 0

Views: 1264

Answers (1)

bosco_yip
bosco_yip

Reputation: 3802

In B2, formula copied down :

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

enter image description here

Edit :

If your 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)," &",". &")&".","..",".")

Upvotes: 1

Related Questions