Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Multiple Full Stops (Periods) Errors in Excel

    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?

  2. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by Antediluvian View Post
    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!
    Last edited by jeremybarker; 2014-05-10 at 04:32. Reason: Extra info

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    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

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •