Home » Questions » Computers [ Ask a new question ]

Excel displays ### for long text - what's wrong?

Excel displays ### for long text - what's wrong?

We've got an excel sheet with a pretty long text in one cell. Instead of line breaks (as set in the cell format), we get a long line of ####s. We can go back and forth by deleting and adding chars at the end of the text.

Asked by: Guest | Views: 34
Total answers/comments: 5
Guest [Entry]

"If the cell width is too short, Excel either simply cuts the visible text off, or it flows into the next cell (depening if the next cell has some content in it or not).

Excel displays ### when the cell content contains just text and it exceeds 256 characters and the cell format is set to ""Text"". Usually, setting the cell format to ""General"" fixes this problem.

However! If you use this cell as a data-input to, for example, a field in a merged Word document, only the first 256 characters will be grabbed!!!

I have not found a fix for this as of yet, but would like to know a solution for the later problem."
Guest [Entry]

I believe that you just need to make the cell a little wider - this (####s) is what excel does when it cannot display your whole entry.
Guest [Entry]

"The display problem should be solved by changing the cell to ""general"" rather than ""text"" - thanks to Microsoft for that intuitive solution!

The other problem, of copying >256 characters from WORD, is solvable by cutting the actual text from WORD then, in edit mode in the relevant cell, pasting the text into the formula bar above the sheet."
Guest [Entry]

"It happens if the text exceed 256 characters, hence it shows ######.

Some of our friend suggest to change from Text to General, but Excel error may pop up:

So, the solution is simple, just begin your text with just adding the ' symbol, in order to neglect any setting of the column, yet you still have to use General Format."
Guest [Entry]

"So many oddball attempts at answers that don't even address the original question posted on Nov 4 '09!

The post date implies either Excel 2003 or 2007.
although not stated, the user might have the text in the cell as a formula: =""256+ chars of text here"". This is where the 256 char limit hits - on formulae! This has nothing to do with row height or column width. Maybe people are just too young to know about 8 bit architectures?
Excel DOES give the solution in its error box but it's quite obtuse.

Amirull provided the correct fix on Nov 27 '12:

ensure the cell is formatted as General
place a single quote as the first char of text. You can remove any equal sign and/or double-quotes. The single-quote must be some sort of escape character for older versions of Excel.

Hope this helps since the solution IS not documented by MS."