How to Remove Excess Periods

The other day I came across the most annoying formatting that was a major pain-in-the-ass undo.
. Envelopes………………………..
. Paper……………………………….
. Staples…………………………….
Imagine having to go into each cell, highlighting a bunch of little dots and then deleting them. Then repeat this a couple hundred times. YIKES!!! So, on my journey to work smarter and not the other way, I discovered something interesting. Excel thinks that 1 period is a single charter and that 3 periods is also a single character. So, what do you do when you’ve got butt-load periods? Which number of periods do you try to eliminate? The answer is you have to deal with both. Here are the character numbers:

. = CHAR(46)
… =CHAR(133)

I won’t bore you with the details, but here is the magic formula that did the job:

=LEFT(A1,(IF(ISERROR(FIND(CHAR(133),A1,1)),(IF(ISERROR(FIND(CHAR(46),A1,1)),LEN(A1),FIND(CHAR(46),A1,1)-1)),FIND(CHAR(133),A1,1)-1)))

I am using the LEFT function which returns a specified number of characters starting at the left of the target cell. Most of this monster formula is just finding how many characters that needs to be returned.