How do I count rows within a cell?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jouni Kananen - 24 Oct 2006 13:52 GMT

Hi,

I need to count the number of rows within one cell. Values have been input
using Alt+Enter to cut the data on separate rows.

If if helps, I could also count how often ".h" appears in the cell because
the data are C header files...

I'd like to use something built in. I can write a macro of my own if that's
the only option.

BTW, is there a max number of rows a cell can take in?

Thanks in advance,
Jouni

Reply to this Message

Bob Phillips - 24 Oct 2006 14:01 GMT

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

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Hi,
>
[quoted text clipped - 11 lines]
> Thanks in advance,
> Jouni

Reply to this Message

Jouni Kananen - 24 Oct 2006 14:26 GMT

Works perfectly, thanks Bob!

Jouni

> =LEN(H1)-LEN(SUBSTITUTE(H1,CHAR(10),""))+1
>
[quoted text clipped - 16 lines]
>> Thanks in advance,
>> Jouni

Reply to this Message

Gord Dibben - 25 Oct 2006 01:14 GMT

Jouni

Cells will hold 32767 characters, but will display only 1024 characters
normally.

The ALT + ENTERS will increase that 1024 limit quite a bit if entered every 100
chars or so.

Also the row height limitation will curtail display if the lines are quite
short.

Gord Dibben MS Excel MVP

>Hi,
>
[quoted text clipped - 11 lines]
>Thanks in advance,
>Jouni

Reply to this Message