Count Commas in Cells

[Guest]

I'm looking for a formula that will count commas or count items deliniated by
commas in cells. I've got table data in column A like:

A1=red, blue, green
A2=blue, Fred
A3=Can, jar, stack

And I need to count the elements in each cell. So, after the formula the
cells would list:
B1=2
B2=1
B3=2
(if counting commas) or:
3
2
3
(if counting items)

I then ultimately plan on running a piviot table off of Column B to indicate
by percent of the entries have 1, 2, 3, 4, etc. items listed in them.

Anyone got a way of doing that?

Thanks in advance for your help!
- Dax

 

[Guest]

Try something like this:

With
A1: (containing a comma delimited list)

This formula counts the commas:
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

and...this one counts the items
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

This formula will give you the number of commas in a cell:

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

And for counting items, just add 1 to the above formula.

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

HTH,
Elkar

 

[T. Valko]

Try this to obtain the count of elements in a cell:

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

Copy down as needed.

Biff

 

[Guest]

You guys are awesome! Thanks so much!

Try something like this:

With
A1: (containing a comma delimited list)

This formula counts the commas:
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

and...this one counts the items
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

You're very welcome....and thanks for the feedback.

***********
Regards,
Ron

XL2002, WinXP