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.

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

Solution 1:

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.

Solution 2:

There are two reasons:

A) 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. 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:

B) 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.

Solution 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.