Microsoft Excel 2007 - Cannot Fit All Text Into Cell

Question

Is it possible to fit a large amount of text within a cell in Excel 2007?

I have 1,890 words - consisting of 10,110 characters (without spaces) - that I need to put into a single cell in Excel. I have set the cell to the maximum size (column width of 255 and row height of 409.5), yet it fails to contain all of the text. I have also set the text to the minimum size - size 1 (even though it is unreadable) - just to see if this helps, but it does not unfortunately.

I am required to keep all of the text in a single cell, so cannot branch into surrounding cells.

Is there a way around this that I am missing or overlooking?

Many thanks.

Answer 1

There are two points here; containing and displaying.

Per Microsoft 32,767 characters is the max a cell can contain. So yes you can fit 10,110 characters in the cell.

As allready discussed in other answers, no you can not display that many characters in a fashion that will be readable.

Reference http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx

Answer 2

Do you see just #### or is your text just not displayed fully?

When you see just ###, then you might have formatted this cell as text - although this is correct, it limits your view to 255 Characters. You can easily test this with the REPT function, using a single character and 255 or 256 repetions. Then copy this as a value to a text-formatted cell or a default-formatted cell.

So, in order to see your characters, you need to use another format - like default.

On the matter of full display of this amount of characters, you will be limited to the max size of a cell (255-409,5).

Answer 3

I had the same problem with Excel 2007. I copied the information from Excel to Word and paste it back to Excel and it worked.

Answer 4

Possible suggestion. I believe the text actually fits in a cell. The problem is showing it (which I believe excel will not do).

So, divide the problem into two parts: store the text in one cell. Show it in several. You can extract text from the storage cell to the display cells using =Extext(...) funktion.

Answer 5

Maybe this helps:

"However, you can increase the number of characters displayed if you add line breaks in the cell (press Alt+Enter)."

Source: http://www.contextures.com/xlfaqApp.html#CharInCell

Answer 6

You can use the 'Wrap Text' option under Alignment group of the Home tab. Wrap Text option allows you to place text on multiple lines within a single cell rather than have the text spread over multiple cells in the worksheet.

Note: Although the screenshots are for Excel 2010, this should work in Excel 2007 as well.

Here's a quick how to:

1. Type or paste the text into a the cell you want. In this example, I have used the rand function in Word then copied and pasted the text in cell A1.

   
   ^{(Click images to enlarge)}

   

   As seen above, the entire text borrowed spaces to multiple cells up to column CJ.

2. Select the cell that contains the text. In the example, that would be cell A1.

   Go to Home tab → Alignment group → Wrap Text.

   

   Here's the sample output:

   

   This sample output consists of 1,665 words with spaces and font size of 8. It is still readable in Excel.

Answer 7

Unless there is a particular reason the data must remain in Excel, a far better workaround than anything I've seen suggested here would be to copy and paste the data into a table in Word, which has no such limitations. To do so, select the entire range of cells potentially containing data,then press ctrl-c to copy it. Then open a new word document. (If the table is very wide, you may want to set the page orientation in the word document to landscape, and or change the paper size to something wider, such as legal.) Now hit ctrl-v to paste the Excel data into the Word document; it should paste as a Word table. (If the table exceeds the page/margin width, simply go into the table properties, clear any specified column widths, and set the table size to 100%.)

Remember that Excel is not really designed or intended to be a tool for handling large chunks of text. If that is what you need to do, a good word processor, as Microsoft Word, is nearly always a better choice.

Answer 8

I feel like this is an easy fix once you figure out how, but no one actually answered the question...

First, you may just have way too much text, and your best option is to merge cells. For general auto-formatting, here is the solution:

In Excel 2007 go to "Home" > "Cells" section > "Format" drop bar > "Autofit row height" and "Autofit column width"

Problem solved!

Answer 9

MS reckons that the longest formula you can have is 8192 characters. So it would seem not!

http://office.microsoft.com/en-gb/excel-help/excel-specifications-and-limits-HP010073849.aspx