Number of Charaters allowed in an Excel Cell?

  • Thread starter Thread starter Zel Dolinsky
  • Start date Start date
Z

Zel Dolinsky

This seems like an easy question but I can't seem to find the answer. Does
anyone know if Excel has a limit to the number of characters allowed in a
cell? Thanks.
 
Help / keyword:= specifications

Length of cell contents (text) 32,767 characters. Only 1,024 display
in a cell; all 32,767 display in the formula bar.
 
But you can see lots more than 1024 characters if you add alt-enters (to force
new lines within the cell).
 
Ken Wright said:
Help / keyword:= specifications

Length of cell contents (text) 32,767 characters. Only 1,024 display
in a cell; all 32,767 display in the formula bar.
Click to expand...
....

And the specs in this case are pure BS (maybe they were written by MSFT's
marketing or legal departments), as you should know by now.

As Dave Peterson has already pointed out, if newlines are inserted at
regular intervals, you can see most of those 32K characters in suitably
sized cells. And as for the number of characters that appear in the formula
bar, unless the system typesize is set to 4 points and screen resolution to
greater than 1600x1200, you'd need to scroll the contents of the formula bar
in Edit mode in order to see all the characters. (250 chars/line by 100
lines in screen, so unreadable unless you're using an HDTV > 1 meter
diagonal screen size, and that'd only be 25,000 chars on screen.)
 
LOL - OK I give in, I was just plain lazy with this one, my answer was a
'tad' less than comprehensive, and you and Dave have filled in all the holes
for me :-)

Hopefully if I grovel and beg for forgiveness now then no-one else will come
in and beat up on me <g>

--
Regards
Ken.......................


<snip>
 
However, there is another factor that limits the maximum number of
characters you want to enter in a cell to 255. If you copy a
worksheet and a cell has more than 255 characters you will get a pop
up message saying: "The sheet you are copying contains cells that have
more than 255 characters. When you copy the entire sheet only the
first 255 characters are copied. To copy all of the characters, copy
the cells to a new sheet instead of copying the entire sheet." There
is only a button that says OK. There is no way to cancel the copy.
It happens, and longer cells are truncated. I have a request to
Microsoft -- add a way to copy the sheet with long cells anyway! This
limitation seems to be left over from years ago. Why ask the user to
find those large cells and copy them by hand. Also, add a Cancel
button!

You can find all these long cells by searching on a pattern that has
256 questions marks. The question mark is the wild card meaning match
any character. This pattern also finds cells that are longer than 256
characters.

Steve Tolkin

P.S. How can I contact Microsoft to actually make this an official
request?
 
You can copy all the cells by doing:
insert worksheet, on original worksheet select all, copy,
then paste to new sheet.
Unfortunately the new sheet loses the column widths and rows heights.
They would need to be set manually.
What else is lost by doing this, compared with copying the sheet?
What happens with cells names, etc?
 
Replying to myself (again)
I think you can copy a sheet with cells longer than 255 characters by:
1. copy the sheet (ignoring the warning about long cells)
The point is to keep the column widths and row heights.
2. select all (ctrl+a) and copy all the cells from the original sheet
3. If the first sheet is protected turn protection off the new sheet
4. Select the new sheet and go to cell A1 and then paste
5. If the first sheet is protected set protection on for the new sheet

Will this be an exact copy?
I do not know how this will work for cell names, hidden cells etc.

Steve Tolkin
 
It'll be as exact as it can be.

If you do this within the same workbook, then the names on the pasted sheet will
become local (sheet2!mynamehere). But that's about all that can happen.

And the other benefit of doing this is that you get all the objects
(charts/buttons/checkboxes) and the all the code behind that worksheet copied,
too.
 
Back
Top