add period when needed

Manolocs

Active Member
Joined
Mar 28, 2008
Messages
339
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

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

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,352
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
 
Last edited:

Manolocs

Active Member
Joined
Mar 28, 2008
Messages
339
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,148
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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),".,:?!")),"",".")
 
Last edited:

Manolocs

Active Member
Joined
Mar 28, 2008
Messages
339

ADVERTISEMENT

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,148
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Active Member
Joined
Mar 28, 2008
Messages
339

ADVERTISEMENT

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....
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,148
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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)?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,148
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,136,314
Messages
5,675,028
Members
419,544
Latest member
MVPDoug

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top