Help with LEN and Substitute function

		Microsoft Office Forums > Microsoft Excel > Excel
Help with LEN and Substitute function

FAQ

Thread Tools

Display Modes

Natalee Help with LEN and Substitute... 11-07-2014, 07:25 PM

whatsup Hi Nat I rather would go... 11-07-2014, 07:59 PM

Natalee Hi whatsup, This actually... 11-07-2014, 08:10 PM

Natalee Hi whatsup, Update...It... 11-07-2014, 08:15 PM

whatsup Now that seems to be a... 11-08-2014, 05:28 AM

Natalee Thanks I see what you mean.... 11-08-2014, 07:56 AM

11-07-2014, 07:25 PM

Natalee

Windows 8

Office 2013

Novice

Join Date: Nov 2014

Posts: 6

Help with LEN and Substitute function

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

« Previous Thread | Next Thread »

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

Other Forums: Access Forums

All times are GMT -7. The time now is 08:52 PM.