Add Full stop to all cells

[Manolocs]

I have this extensive list some of it already have a full stop at the end of the phrase, I need to insert the (.) at the end of the cells that do not have full stop already.
Any help is more than welcome. If possible with formulas would be very appreciated. Thanks in advance

 

[Jaggy]

In the column beside where your data is, use this formula. =IF(RIGHT(G7)<>".",G7&RIGHT("."),G7)

then you can paste special values back over your original list and delete the column with this formula.

 

[Manolocs]

Hi Jaggy I tried this formula before the problem it adds "." to all cells even the ones that already have "." so many of then will have ".." at the end.

 

[Jaggy]

When I tested this out it worked fine. Are you sure you copied the formula exactly the way it is (changing the cell references to suit your sheet)? The IF statement checks for the period at the end of the string and only applies it if it is not there.

 

[Manolocs]

Yes I am sure, I have no idea why behaves like this, the formula says if there is no "." copy cell, but is not working

 

[Jaggy]

how about this formula?
=SUBSTITUTE(G7,".","")&"."

This removes existing periods if they exist and adds one to the end. Only issue with this is if your string has multiple full stops in it; it will remove them all. For example:
"Hi, I live in Canada. It is cold today." will be changed to "Hi, I live in Canada It is cold today."

 

[Manolocs]

It is a good idea but there are many end points in the middle as well.

 

[Jaggy]

Ok, so this is my last shot at this because I'm out of ideas. I stole the idea from this forum here: Excel: last character/string match in a string - Stack Overflow

Assuming your data is in K16, put this formula in L16.
=IFERROR(IF(LEN(K16)=FIND("@",SUBSTITUTE(K16,".","@",LEN(K16)-LEN(SUBSTITUTE(K16,".","")))),K16,K16&"."),K16&".")

Essentially, this looks for the last occurrence of a full stop and checks to see if it matches the length of a string. If it doesn't, it'll add the full stop. Now, if there is no full stop at all in the string, that is where the IFERROR kicks in and adds one to the end.

If this doesn't work, you may need one of the wizards on this site to provide you some VBA code.
Good luck!

 

[Joe4]

Please post some examples of your data that you are trying to apply this to.

 

[Blunder1]

Are there any spaces at the end? try
=if(right(trim(g7),1)=".",trim(g7),trim(g7)&".")

may have to adjust the syntax..