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.

Answer 2

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 3

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 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:

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

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

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

As far as I know, Excel only does the # thing for number formats that it cannot wrap - mainly dates. I don't know of any setting or combination of settings that will result in # signs in a cell containing text.

What exactly does the problematic cell contain, and what are the format settings?