Help with LEN and Substitute function

Natalee · #1 11-07-2014, 07:25 PM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
#REF! Error in calling VBA function disappears when function is copied	lcaretto	Excel Programming	2	05-26-2014 07:19 PM
Creating a graph for Future Value function (FV function)	bmoody	Excel	2	11-06-2013 10:52 AM
Find & Replace: substitute red-coloured words with underscores	tinfanide	Word	2	10-06-2012 11:04 PM
how to use now() function	gsrikanth	Excel	1	01-07-2012 01:18 AM
if function help	jim831	Excel	2	10-29-2010 07:06 PM