Knowing where text will wrap

[Alan]

I have a long text string (>1300 characters). Even with text wrapping
and expanding the cell height, it eventually (at 1200-something
characters) stops wrapping.

I want to be able to tell where the text begins to exceed the cell
width (i.e., stops wrapping), so I can end that cell nicely and put
the rest in subsequent cells. I know how to do the latter.

Is there a way to tell via (VBA) where the text begins to exceed the
cell width?

Thanks, Alan

 

[Chip Pearson]

That is a known limitation with Excel. The content can be up to 32K,
but the display is limited to somewhere around 1200. The can sometimes
be extended by putting a few lines breaks in the string (ALT ENTER to
enter a line break) but you probably won't get the full content to
display.

You might try dropping a TextBox control on the worksheet and use the
SelectionChange event to put the content of the active cell into the
TextBox. This is far from ideal, but it might be the only work-around.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Alan]

Chip,
Is there a way to tell via (VBA) where the text begins to
exceed the cell width?

Alternatively, is there a way to find out where the last word (in
a < 1200 character string) ends, with respect to the right border of
the cell? If so, then I could do a simple fix to the code I have.

Thanks, Alan

 

[Dave Peterson]

If you add those alt-enters every 80-100 characters, you'll be able to see lots
more than 1300 characters.

If you don't include those alt-enters, you may be able to look at
len(myCell.text). It showed 1024 in my simple testing, though.