Can You Count This Way?

[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.

 

[Frank Kabel]

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

 

[Timothy Wright]

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

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.

 

[Frank Kabel]

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

 

[Timothy Wright]

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

Thanks.

 

[Peo Sjoblom]

Try

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

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

 

[Frank Kabel]

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

 

[Timothy Wright]

Try

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

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

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.

 

[Frank Kabel]

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

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

 

[Timothy Wright]

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

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

 

[Peo Sjoblom]

Try

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

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

 

[Frank Kabel]

Hi Peo
thanks for the correction
Always forget these parenthesis

 

[Timothy Wright]

Hi Peo
thanks for the correction
Always forget these parenthesis

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.

 

[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