Can You Count This Way?

T

Timothy Wright

I have some cells with note type data:

For ex:

ax,3b7,22,xy,zz,

Is there a way to count every item between the commas?

Thanks.
 
T

Timothy Wright

Hi
try
=LEN(A1)-LEN(SUBSTITUTE(A1,",","")+LEN(A1)>0
Click to expand...

Um, that didn't work. Maybe I should clarify. If my cell looks like
this: x26,zz,7,42b,22,949,xx, then I want to count each item between
the commas as a single unit. So the answer for my example would be 7.

In your formula do I do anything with the (A1,",",""), or do I put it
in exactly as shown?

Thanks.
 
F

Frank Kabel

Hi
just use the formula as posted (just change the cell reference A1
according to your needs)
 
T

Timothy Wright

Hi. Sorry to be so obtuse, but I still can't get it to work. It
always returns the error, "value!

Thanks.
 
P

Peo Sjoblom

Try

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

that would return 6 for your example, it basically counts the commas
 
T

Timothy Wright

Try

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

that would return 6 for your example, it basically counts the commas
Click to expand...

Hi
sorry, missed a bracket. try
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+LEN(A1)>0

All right now we're getting very close. The first formula, like you
say, counts the commas, which is a workable solution. The only thing
I need to do is make the formula add one more to the answer so I don't
have to enter a comma after the last entry.

Ex: 66,23,x7x,4x2,23u,

Adding a comma to the last entry works if I remember to do it. How
would I change the formula so it adds one to the answer?

I still can't get the second formula to work. This time it returns
"true."

Thanks.
 
F

Frank Kabel

Hi
either use:
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

or
=LEN(A1)-LEN(SUBSTITUTE(A1,",","")) + LEN(A1)>0
 
T

Timothy Wright

Hi
either use:
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1
Click to expand...

Thanks for the help you guys I appreciate it. One more question and
I'll be out of your hair forever. ;-)

What if there is nothing in the cell? How do I make the answer come
up zero?

Thanks,

Timothy
 
P

Peo Sjoblom

Try

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+ (LEN(A1)>0)

note the extra parenthesis around the last LEN, otherwise you'll get FALSE
 
T

Timothy Wright

Hi Peo
thanks for the correction :)
Always forget these parenthesis
Click to expand...

OK, I finally got this formula to work. What was happening is that
every time I would put this formula into a cell the whole formula
would show up in the cell instead of an answer. I have no idea why it
works now and wouldn't work yesterday. I'd copy and paste the formula
into the appropriate cell, change the relative cell reference numbers
and then instead of an answer the whole formula would show up in the
cell. Very perplexing.

Thanks much for the help.
 
F

Frank Kabel

Hi
reason for this could be that the cell was formated as 'Text'. change
this cell format to 'General' and re-enter the formula.
Another reason could be that in the dialog 'Tools - Options - View'
'Formula' was checked
 
Top