38

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?

3
  • 5
    I guess the column length is not enough to display the cell data.
    – ukanth
    Nov 4, 2009 at 12:58
  • Any formulas on that cell?
    – Palantir
    Nov 4, 2009 at 13:02
  • Just make the column larger.
    – Rexcirus
    Sep 12, 2022 at 12:00

16 Answers 16

60

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.

5
  • 3
    +100 setting the cell format to General saved me.
    – Neil N
    Aug 19, 2010 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. Sep 7, 2011 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, 2011 at 13:59
  • "General" does the trick.
    – anon
    Mar 2, 2012 at 19:53
  • It's like the difference between 'string' and 'text', First consider if you have more than 256 characters. Then set cell format. Otherwise it's a column width 'view' issue.
    – Jerome
    Aug 13, 2014 at 11:29
24

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.

5
  • 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, 2009 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, 2009 at 13:44
  • I typed a long string of text into a cell, but could not get it to display the #'s.
    – Geoffrey
    Nov 5, 2009 at 10:19
  • I get it when I have a calculated value which is longer on a subsequent recalculation.
    – ChrisF
    Nov 5, 2009 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. Sep 7, 2011 at 15:51
6

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.

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.

1
  • Yes, I recently ran into the same problem and discovered the "change format from Text to General" fix. It worked, but it doesn't make sense to me why it's necessary. But there it is!
    – DaveU
    Jan 25, 2023 at 23:33
5

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

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.

1

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:

Excel still doesn't allow you if use General

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.

1

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:

  1. ensure the cell is formatted as General
  2. 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.

1

Excel is Garbage. It will always be garbage. No person in any instance of reality will ever want to see #### instead of the content that is in the cell. If the cell is not "displayed wide enough", it should show the beginning of whatever string, number, date, ANYTHING that the user typed in so that it is useful and not just garbage. But it doesn't. It never will. Excel is the scummy foam that stays in the toilet after you flush vomit. It will always get worse. It has no actual purpose. Anything you ever try to accomplish with excel will leave you searching forums for "why the f*** does excel do this and how can i make it do something productive, or at the very least, not do something counter-productive?" Sadly the answer will always be, "you cant." I assume people with money somewhere created and control this application and enough people to force its ubiquity and are using it to ensure that there will never be competition in the being rich department. Just in case I didn't mention it, EXCEL IS GARBAGE. You are better off using a physical ledger made of bee stings, razor blades and lemon juice and writing in it with your face. This should be the answer to all excel related question as its purpose seems to be only the torture of the human race. Good luck. Try not to commit suicide while smashing your head over and over against the screen of your computer. Though they probably read this post and laugh at their accomplishment, don't let the terrorists win

2
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Oct 7, 2021 at 19:29
  • This is hilarious
    – ZygD
    Oct 7, 2021 at 19:57
0

Go to:

"Tools">"Options">"Transition"

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

1
  • 1
    Did you mean apostrophe or hash character?
    – Lee Taylor
    Nov 27, 2012 at 11:34
0

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.

1
  • 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, 2013 at 18:16
0

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

0

First check if the column width is wide enough for your input. If this is not working, clear all cell formatting and enter your text again.

0

"General" format and "Wrap Text" fix the problem.

In terms of copy and paste, if you use the F2 function key to enter the editing mode, and highlight the section of texts (could be more than 256 chars) and copy, you can paste the entire selection into other apps like Excel and Word etc.

0

I wanted a two line time stamp (date plus time) so the column width was appropriate for the other data in my column:

3/2/2020 9:41 pm

The only way to get this to display was to use the text function, with an ascii carriage return. MK4 and ML4 are the cells with the date and time I wanted to use in this summary section.

=TEXT(MK4,"mm/d/yy") & CHAR(10) & TEXT(ML4,"h:mm A/P")

0

I also faced the same problem once but I didn't find any suitable solution. I copy pasted the text into another cell and that solved the problem. I had to copy paste the text of all the cells one by one to the new cell and got rid of the problem. In this way I have moved the text from each cell of the column to the new cell and created the new column and then removed the previously blank column.

1
  • 1
    How is this better than accepted answer?
    – Toto
    Dec 30, 2021 at 13:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .