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

Question

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.

We've played around with cell formatting etc. but no success. What can we do?

Answer 1

All it means is that the text is too long for the cell at its current width.

Make the cell wider (double click on the header border) to see all the text.

UPDATE

I've just seen the other answer that claims this is wrong. It's not. Here's a screen shot of part of a spreadsheet:

Columns P and Q contain the same data - monetary values of the same order of magnitude. P is wide enough to display the text Q isn't. As you can see it's displaying "#####". If I double click on the header for Q it becomes wide enough again:

Another case is where the column is a date and the value is out of range. If you enter "5555555555555555555555" (say) into a date column you get a series of "#####" and the tooltip states:

Dates and times that are negative or too large display as ######.

There may well be another case where Excel prints "#####" - but these are by far the most common.

Answer 2

The top answer here (that one at the top with the most votes) is completely wrong!!! This does not have anything to do with the cell width....

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.

Answer 3

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.

Answer 4

How long is the cell text? What is the cell format?

To edit cells with long text expand the formula bar (Ctrl + Shift + U).

If you just need to display an extract from the cell, you can use a formula like this to display up to the first 10 characters:

=IF(LEN(A1)<=10, A1, CONCATENATE(LEFT(A1,10-3),"..."))

where:

• A1 is the cell with your long text.
• 10 is the maximum number of characters you want to show.

Answer 5

I was having a similar problem recently where wrap text was turned on but once the text in a cell reached a certain length ##### was displayed regardless. I found changing the format of the cell to "General" fixed the problem.

Answer 6

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.