How not to count blank cells

[mrc44]

I have this formula

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

which counts the comma separated values in cells. My problem is the formula counts the blank cells which contains formulas as 1. How can I fix this? thanks.

for example:

if B4 is {1,2,3}, the result will be 3.
if B4 is {25}, the result will be 1.
if B4 is {}, the result will be 1. (which should be 0).

 

[mrc44]

ok, thanks. I found solution here.

[COLOR=#574123][COLOR=#333333][INDENT]=IF(LEN(D8)>0,LEN(D8)-LEN(SUBSTITUTE(D8,",",""))+1,0)[/INDENT]

[/COLOR]


[/COLOR]
[COLOR=#574123][B][RIGHT][/RIGHT]
[/B][/COLOR]