Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Nov 4 '09 at 12:51

This question came from our site for professional and enthusiast programmers.

4  
I guess the column length is not enough to display the cell data. –  ukanth Nov 4 '09 at 12:58
    
Any formulas on that cell? –  Palantir Nov 4 '09 at 13:02
add comment

9 Answers

up vote 37 down vote accepted

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.

share|improve this answer
3  
+100 setting the cell format to General saved me. –  Neil N Aug 19 '10 at 20:52
1  
The top answer is correct on some of the reasons that ###### might appear in the cell, it just doesn't cover this specific reason (for which your answer is correct). You might want edit your first line to let people know that. –  Lance Roberts Sep 7 '11 at 15:53
    
+1 This answer was the correct one for my case. But, as @LanceRoberts mentioned, the other answer is correct as well for the typical case of text being longer than the column width. –  omarello Nov 9 '11 at 13:59
    
"General" does the trick. –  anon Mar 2 '12 at 19:53
add comment

Simply right click on cell and select "format cells" and change selected "category". This will help resolve issue.

share|improve this answer
add comment

Check the formatting by:

  1. Right clicking on the cell
  2. select "Format Cell"
  3. Click on the "Number" tab at the top of the instruction box
  4. Select "Custom" in the box on the left
  5. Then select "General" in the box to the right.

All text should be seen at this point.....hope this helps.

share|improve this answer
    
Good description of the process to format, but in their question they noted they had "played around with cell formating"-how does your answer factor in their previous attempts? –  dav Apr 30 '13 at 18:16
add comment

Go to:

"Tools">"Options">"Transition"

and un-check the transition navigation keys will stop the apostrophe displaying in the formula bar

share|improve this answer
1  
Did you mean apostrophe or hash character? –  Lee Taylor Nov 27 '12 at 11:34
add comment

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 too narrow

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:

Columns wide enough

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.

share|improve this answer
    
Exactly. Nothing wrong here, typical Excel behavior to let the user know that the source text is longer than the box can display. –  KdgDev Nov 4 '09 at 13:20
3  
You can also try clicking the "wrap text" and "shrink to fit" options under formating to get the text to fit. –  Matt Nov 4 '09 at 13:44
    
I typed a long string of text into a cell, but could not get it to display the #'s. –  systemovich Nov 5 '09 at 10:19
    
I get it when I have a calculated value which is longer on a subsequent recalculation. –  ChrisF Nov 5 '09 at 10:53
    
The answer is correct about some of the reasons you can get ###### in a cell, but in the specific case for the OP, CookieRevised's answer is the reason. –  Lance Roberts Sep 7 '11 at 15:51
add comment

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.

share|improve this answer
add comment

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.
share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.