Count no. of lines of text in a text wrapped cell

Hi All,

I am trying to get the count of the number of lines of text in a cell that has text wrap applied to it. To clarify the text has no manual returns, but the cell may be a page wide and has maybe 3-6 lines of text where excel has moved text to new lines in order to keep within the column.

I've tried the following formula:

=LEN(B4)-LEN(SUBSTITUTE(B4,CHAR(10),""))+1

But as there are no actual keyed returns, the result is always 1.

Any ideas? I'm trying to ascertain the value so that I can use it to set the row height more accurately than autofit, which is so inconsistent.

Thanks

mjmitch

Cheers Glenn,

See attached file. How do I get the result in D4 to be 5?

Len Formula.xlsx

Thanks All

No, but the thread was closed by admin. It works but just wasn't sure if that was the most efficient way to code it to run through a large number of cells

Please Login or Register  to view this content.