count number of entries in cell

[mkk]

Hi,

I am trying to count the number of entries in a cell. For eg in cel
A15..I have rat,cat,mat,fat....The function should return 4. Is there
inbuilt function to do that or what would a macro to do that loo
like.

Thanks,
M

 

[Frank Kabel]

Hi
are all your entries separatet by a comma. If yes try
=LEN(A15)-LEN(SUBSTITUTE(A15,",",""))+1

 

[Bob Phillips]

As long is the format is consistent

=LEN(A13)-LEN(SUBSTITUTE(A13,",",""))+1

--

HTH

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

 

[mkk]

thanks guys..it works perfectly...just so i understand.. how exactl
does it work?

thanks
M

 

[Bob Phillips]

It works on the basis that the number of items is equal; to the number of
delimiters (comma) + 1.

What it does is first to count the characters in the cell (=LEN(A1)), all of
them

Then it substitutes all instances of comma with nothing, and counts this
amended version (LEN(SUBSTITUTE(A13,",",""))) which is all in memory, not a
worksheet cell

It takes the comma-less count from the total count, adds 1, voila, the
number of items.

--

HTH

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

 

[mkk]

ah! thanks!! but theres a catch..i have all these numbers/text being fe
in by other spreadsheets. In a lot of cases these cells are empty bu
do have a formula. If i do this on a blank cell it still return 1

 

[mkk]

I tried using a if but still doesnt work..

=IF(H11<>"",LEN(H11)-LEN(SUBSTITUTE(H11,",",""))+1,LEN(H11)-LEN(SUBSTITUTE(H11,",",""))

 

[Bob Phillips]

And the problem is ?<g>

=IF(H1="","",LEN(H1)-LEN(SUBSTITUTE(H1,",",""))+1)

--

HTH

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

 

[mkk]

the problem is that even if the cell contains nothing (except
formula)...this cell counting formula still returns a 1.

thank

 

[Bob Phillips]

I know, I added a grin at the end<g>

And then I gave a solution .

--

HTH

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

 

[mkk]

thanks...but that still doesnt work..it still returns 1 in the empt
cells. Is that because I have a formula in that cell?

Thank

 

[Dave Peterson]

This formula returned 1 for cells that looked empty (evaluated to "")?

=IF(H1="","",LEN(H1)-LEN(SUBSTITUTE(H1,",",""))+1)

Maybe your formula evaluated to " " (a space character)?

If yes, you can get use this:
=IF(TRIM(H1)="","",LEN(H1)-LEN(SUBSTITUTE(H1,",",""))+1)

But I think I'd take the time to change the original formulas:

If I had something like:
=if(a1="asdf","ok"," ")
I'd change it to:
=if(a1="asdf","ok","")

Those extra spaces are a pain to work with.

 

[mkk]

Thanks a lot! that works perfectly! Yeah these invisible blank space
drive me crazy!

Thanks again,

M