count number of entries in cell

  • Thread starter Thread starter mkk
  • Start date Start date
M

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
 
Hi
are all your entries separatet by a comma. If yes try
=LEN(A15)-LEN(SUBSTITUTE(A15,",",""))+1
 
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)
 
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)
 
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
 
I tried using a if but still doesnt work..

=IF(H11<>"",LEN(H11)-LEN(SUBSTITUTE(H11,",",""))+1,LEN(H11)-LEN(SUBSTITUTE(H11,",",""))
 
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)
 
the problem is that even if the cell contains nothing (except
formula)...this cell counting formula still returns a 1.

thank
 
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)
 
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
 
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.
 
Thanks a lot! that works perfectly! Yeah these invisible blank space
drive me crazy!

Thanks again,

M
 
Back
Top