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?
216 Answers
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.
5There 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.
5I 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.
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:
A1is the cell with your long text.10is the maximum number of characters you want to show.
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.
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.
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:
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.
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:
- ensure the cell is formatted as General
- 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.
Go to:
"Tools">"Options">"Transition"
and un-check the transition navigation keys will stop the apostrophe displaying in the formula bar
1Check the formatting by:
- Right clicking on the cell
- select "Format Cell"
- Click on the "Number" tab at the top of the instruction box
- Select "Custom" in the box on the left
- Then select "General" in the box to the right.
All text should be seen at this point.....hope this helps.
1Simply right click on cell and select "format cells" and change selected "category". This will help resolve issue.
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.
"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.
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")
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
2I 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
