Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Text appearing as # in text-formatted cell (2003 SP1)

    I have needed to set a format of Text on a range of cells to ensure that if something that looks like a number but isn't is entered in the cells, it stays as text rather than being reformatted automatically. This works fine.

    However, I have discovered that if a piece of text that exceeds 255 characters is entered in the cell, then it displays as a long unbroken string of #'s, like a number that is too wide to be displayed in a narrow column. If I reset the cell format to General (or even Number) the text displays as expected. This problem persists whether or not there are spaces in the text or whether the cell is set to Wrap text and the column width and/or row height are changed. There is something about the format of Text and the magic boundary of 255 characters that Excel does not seem happy with!!


    Anyone come across this before? I understood that Text format meant "display exactly as entered" which is why I've been using it.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text appearing as # in text-formatted cell (2003 SP1)

    This is a strange quirk in Excel. If a cell is formatted as text and if it contains anything from 256 up to and including 1024 characters, it displays a series of # characters. If it contains fewer than 256 characters or more than 1024 characters, the text is displayed correctly.
    So once you reach 256 characters, you'll just have to keep on typing until you reach 1025! <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text appearing as # in text-formatted cell (2003 SP1)

    An alternative would be to format the cells as General, and to start the cell contents with an apostrophe <code>'</code> to force Excel to interpret it as text.

  4. #4
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Text appearing as # in text-formatted cell (2003 SP1)

    Thanks for the advice and extra testing Hans.

    I have a number of workarounds so this is not a major problem, but I was curious to know if it was a known bug, or my misinterpretation of what Text format should mean!!

Posting Permissions

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