Count Items in Cell..Seperated by comma

[ilcaa]

i have 26,0000 rows, each row has a column that lists the products that each client subscribes to...example

client a, prod1, prod2
client b, prod2
client c, prod1, prod3, prod4, prod6

each product they subscribe to is seperated by a comma, i would like to count the number of products for each client. any suggesstions?

 

[AlphaFrog]

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">client a, prod1, prod2</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">client b, prod2</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">client c, prod1, prod3, prod4, prod6</td><td style="text-align: center;;">5</td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=IF(<font color="Blue">A1<>"",LEN(<font color="Red">A1</font>)-LEN(<font color="Red">SUBSTITUTE(<font color="Green">A1,",",""</font>)</font>)+1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=IF(<font color="Blue">A2<>"",LEN(<font color="Red">A2</font>)-LEN(<font color="Red">SUBSTITUTE(<font color="Green">A2,",",""</font>)</font>)+1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=IF(<font color="Blue">A3<>"",LEN(<font color="Red">A3</font>)-LEN(<font color="Red">SUBSTITUTE(<font color="Green">A3,",",""</font>)</font>)+1,""</font>)</td></tr></tbody></table></td></tr></table><br />

 

[doofusboy]

Another approach would be to add a module with a user defined function like this:

Public Function CountProducts(strIn As String) As Integer
    CountProducts = UBound(Split(strIn, ",")) + 1
End Function

Which you can then implement by typing a formula: =CountProducts(A1)

 

[Scott Huish]

I don't think you should be adding 1. The op wanted to count the number of products for each client. In the first example, there are 2 products for the client a, not 3:

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

 

[doofusboy]

good point HOTPEPPER, I missed that too

 

[ilcaa]

thank you for the replies, they worked perfectly and I found that a 1 is needed since the formulas count all the commas, and each comma appears after the product it wont include the last product in the list (since it does not contain a comma after)

 

[Scott Huish]

Right, the formula counts all the commas. All your commas occur BEFORE the product, therefore every product gets counted:

client a, prod1, prod2
(2 commas, 2 products)

client b, prod2
(1 comma, 1 product)

client c, prod1, prod3, prod4, prod6
(4 commas, 4 products)