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" wrote in message
...
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

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)

"Bob Phillips" wrote in message
...
=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" wrote in message
...
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

Your formula is "so cool" - I'd give anything to understand what you (and
the other top responders here) do. Tks

"JMay" wrote in message news:kooZb.6674$iB.2484@lakeread06...
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)

"Bob Phillips" wrote in message
...
=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" wrote in message
...
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