Solve the Mystery of the Truncating Text

[bob.crimmins]

As we all know, the max characters that Excel can store in a cell is
32,767. As we all THINK we know (because Microsoft tells us so), the
max characters that Excel will display or print is 1024. However, this
is just wrong. I have a spreadsheet that includes cells with some very
large text strings (a glossary tab, actually). In many of the cells of
this spreadsheet more than 1,300 characters display and in one of them
approximately 5,500 characters are displayed!! But here's the rub...
within various cells certain sentences or paragraphs are truncated. Of
course, if the particular sentence that gets truncated is the last
sentence then it would appear that the text is truncating because it is
too long to be displayed in the cell... but I am skeptical of that
because so many of the cells display very many more characters.

To observe this bizzzzaar phenomenon, you can download a sample
workbook with examples of two cells that exhit the behavior described
here:

http://sodogroup.com/downloads/BigTextCellSamples.xls

The workbook has only one worksheet in it and no macros so you won't
need to worry about virues or any such things. Alternatively, I have
posted a graphic of the two cells here:

http://sodogroup.com/downloads/BigTextCellSamples.gif

Can you solve the mystery?

Thanks,

Bob

 

[Dave Peterson]

I didn't download either of the files.

But you can see more than the documented 1024 characters by adding alt-enters to
the string in the cell. I try to use them every 80-100 characters.

You may want to check to see if that's what's happening with you. You could
even experiment to determine if you can see more by adding those alt-enters at
that 80 character interval.

 

[bob.crimmins]

Dave,

Thanks for that great observation... it may really help me come up with
a work around. How the question is "what's going on here?" There must
be some logic or pattern in this behavior that can be explained
quanitatively. I'm hoping someone will be able to shed some light on
the internal workings of long text in a cell.

Cheers,

Bob

 

[Dave Peterson]

I have no real knowledge of how long text in cells really works. It's just
experiential (pronounced trial and error).