Multiple Full Stops (Periods) Errors in Excel

Topic

New Reply

I’ve found (to my surprise) some errors in Excel 2003 and Excel 2007. If I have a cell containing more than two consecutive full stops, LEN and SUBSTITUTE do not work properly.

Example: for “….”: LEN(“….”) gives 2, and SUBSTITUTE(“….”,”.”,”B”) gives “…B”.

Does this occur in later versions of Excel, and is there any reason for such anomalous errors? Are any other functions impacted?

Reply | Quote

Replies

I’ve found (to my surprise) some errors in Excel 2003 and Excel 2007. If I have a cell containing more than two consecutive full stops, LEN and SUBSTITUTE do not work properly.

Example: for “….”: LEN(“….”) gives 2, and SUBSTITUTE(“….”,”.”,”B”) gives “…B”.

Does this occur in later versions of Excel, and is there any reason for such anomalous errors? Are any other functions impacted?

How very weird!

I’ve just tried this in Excel 2007 and get the same. With 3 dots in A1 (…), the function LEN(A1) reports 1.
And LEFT(A1,1) gives back … again. Interestingly, CODE(LEFT(A1,1)) gives 133 and CHAR(133) reports …, whereas CODE(.) = 46 and CHAR(46) gives .

EDIT: yes, a quick Google confirms that ASCII code 133 represents the “horizontal ellipsis” – so one dot and two behave as expected, but each multiple of three dots is counted as a single ellipsis!

Reply | Quote

Hi

This is standard ‘auto-correct’ behaviour.
In Excel 2007, typing three consecutive full-stops will be replaced by the single ellipsis character.
If you want to turn this off, in Excel 2007:
Click the Microsoft Office (blob) button
Click Excel Options
In the Excel Options dialog box, click Proofing
Then click the [AutoCorrect Options…] button
..then delete the entry for …

zeddy

Reply | Quote

To further what Zeddy said, boost your magnification to the max allowed (200% in Excel 2010). When you type the 3rd period, note the spacing between the periods. Now either hit Enter (to confirm the entry) or type a 4th period. You should see the spacing between the first 3 periods close up. As Zeddy said, those 3 periods betcame the single ellipsis character. If you want to maintain the first 3 periods as 3 periods, after you’ve typed the 4th character, click undo (or use the CTRL+z combo) and the ellipsis changes back to 3 periods. Or change the Auto-Correct options as he suggested.

Fred

Reply | Quote