add period when needed

[Manolocs]

Hello,
I am trying to fix period when needed, but take in consideration "," ":" "?" when inserting.
The formula I am trying to use is the below but it is adding another period at the end, and inserting a period when the cell is empty.
This formula was made by Special-k99 in another thread.
Thanks in advance

[COLOR=#333333]=trim(b1)&if(or(right(trim(b1),1)=":",right(trim(b1),1)="?"),"",".")[/COLOR]

 

[Special-K99]

Darn! I wish I hadn't read this now

Does this work?

=IF(B1="","",TRIM(B1)&IF(OR(RIGHT(TRIM(B1),1)=",",RIGHT(TRIM(B1),1)=":",RIGHT(TRIM(B1),1)="?"),"","."))

If last character is comma, colon or question mark adds nothing.
If B1 is blank adds nothing.
Otherwise adds a period

 

[Manolocs]

Hi Special-K99 thanks again for your help, it is still adding more periods if there is a period at the end.
Would be great if do not add period if there is a period already

 

[Rick Rothstein]

Hi Special-K99 thanks again for your help, it is still adding more periods if there is a period at the end.
Would be great if do not add period if there is a period already

Does this formula do what you want...

=A1&IF(ISNUMBER(FIND(RIGHT(A1),".,:?!")),"",".")

 

[Manolocs]

it is still adding another period if the text ends in a period

 

[Rick Rothstein]

it is still adding another period if the text ends in a period

Are you replying to my formula? If so, do you perhaps have one or more spaces after the last character? If so, this should fix it...

=TRIM(A1)&IF(ISNUMBER(FIND(RIGHT(TRIM(A1)),".,:?!")),"",".")

 

[Manolocs]

Are you replying to my formula? If so, do you perhaps have one or more spaces after the last character? If so, this should fix it...

=TRIM(A1)&IF(ISNUMBER(FIND(RIGHT(TRIM(A1)),".,:?!")),"",".")

Sorry Rick Rothstein, yes your formula is still adding a period if there are more than one period. Some of the cells have more than one period, but maybe I can search for more than two periods and change it....

 

[Rick Rothstein]

Sorry Rick Rothstein, yes your formula is still adding a period if there are more than one period. Some of the cells have more than one period, but maybe I can search for more than two periods and change it....

We need to see what you see... can you show us several examples of text that you have where the formula doesn't work (periods at the end or any other characters that may not work for you)?

 

[Manolocs]

We need to see what you see... can you show us several examples of text that you have where the formula doesn't work (periods at the end or any other characters that may not work for you)?

Yes of course... please take a look on this:
https://dl.dropboxusercontent.com/u/14313867/Mr.Rick.xlsx

 

[Rick Rothstein]

Yes of course... please take a look on this:
https://dl.dropboxusercontent.com/u/14313867/Mr.Rick.xlsx

The problem with cell A1 is that those three dots are not three individual dots, they are a single character called an ellipsis (which Excel automatically substitutes when it sees three adjacent dots). In the following formula, I simply added the ellipsis character to the list of terminating characters inside the FIND function)...

=TRIM(A1)&IF(ISNUMBER(FIND(RIGHT(TRIM(A1)),".,:?!…")),"",".")

You highlighted cell A5 in red... was that supposed to indicate my formula returned the wrong value? If so, what should it have returned?