Counting number of lines in a CELL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a cell which has a number of lines within it. I want the number of lines it has to come into another cell. Is there any formula or macro for that?

Eg:
A1 content is:
first line
second line
thrid line
Once I use the formula/macro, say A2 will have value 3.

Thanks a lot,
fbhcah
 
=LEN(F1)-LEN(SUBSTITUTE(F1,CHAR(10),""))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

fbhcah said:
Hi,

I have a cell which has a number of lines within it. I want the number of
Click to expand...
lines it has to come into another cell. Is there any formula or macro for
that?
 
Thanks Bob, slight tweak
=LEN(F1)-LEN(SUBSTITUTE(F1,CHAR(10),"")) <<< is 1 off << showing 8
versus 9

=LEN(F1)-LEN(SUBSTITUTE(F1,CHAR(10),"")) +1 << Last Line does not have
Char(10)
 
Your formula is "so cool" - I'd give anything to understand what you (and
the other top responders here) do. Tks
 
Back
Top