Hi,



I am using the LEN and Substitute function to count numbers in a single cell. As an example, I have a cell that contains the numbers 5,15,25,30 and this would give me a count of 4. I am okay with this calculation.

Where I have a problem is omitting the blank cells or cells that contain 0. The function always gives me a 1 even when I added to ignore zeros. This is my formula:

=LEN(SUBSTITUTE(F20&",",",0",""))-LEN(SUBSTITUTE(SUBSTITUTE(F20&",",",0",""),",","") )

Can someone please help me figure out how to get this right?

Thanks,
Nat

Hi Nat

I rather would go with this:
=IF(OR(F20="",F20=0,F20="0"),0,LEN(F20&",")-LEN(SUBSTITUTE(F20,",","")))

Cheers

Hi whatsup,

This actually worked for the 0! Yeah! It still gives me a 1 for a blank cell, but I can get around that. The 0 was my main issue!

Thanks a bunch!! Much appreciated!

Nat

Hi whatsup,

Update...It actually does work on a blank cell, as long as I do not add a number and then delete. This will work just fine overall.

Thanks,
Nat

Now that seems to be a problem the way you delete the contains of a cell. Sticking to F20, if
= LEN(F20)
results in zero, then the formula will work correctly, if you insert for example <space> then it won't. Though the cell appears empty it isn't, and above formula will result in 1.

As explanation, with
OR(F20="",F20=0,F20="0")
you check three cases for F20:
- "" = the cell is empty or (as a result of a formula) contains an empty string
- 0 = the cell contains a 0 as number
- "0" = the cell contains a 0 as text (string)
in either case, the formula will return a fixed result of zero!!!

Thanks I see what you mean.

Nat