Count the number of textual instances in a cell

[darrenj1471]

Hi

Apologies if this question is in wrong forum as my requirement is both a function and a charting question. I have a number of rows on a worksheet and 3 columns (well 3 for the purpose of this question anyway). Column A is a location code (always single value and unique) , Column B is the customers of that location and Column C the suppliers. Column B and C both contain variable values including blank, single value or multiple values seperated by a comma.

I have attached the data, and my end requirement is produce bar charts showing the number of locations with 1-5 Customers, 6-10 Customers, 11-15 Customers, 16 or more Customers and the same for suppliers ie 2 bar charts.

Can you advise how I produce this ie how to do I know how many values are in a cell if they are text seperated by comma and then I can probably go on to generate the chart myself, but if my requirement is clear I would really appreciate help in getting from A-Z

Thanks so much in advance
Darren

[benishiryo]

hi Darren. it'll be good to manually key some answers you are hoping to see, so that we know we are on the right track. if i'm not wrong, for counting customers, you can use:
=IF(B2="",0,LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1)

if you want to categorise them, then put these values somewhere say in J1:K4:
1 1-5 Customers
6 6-10 Customers
11 11-15 Customers
16 16 or more Customers

then:
=LOOKUP(IF(B2="",0,LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1),$J$1:$J$4,$K$1:$K$4)

or if you wanna hard key them:

=LOOKUP(IF(B2="",0,LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1),{1;6;11;16},{"1-5 Customers";"6-10 Customers";"11-15 Customers";"16 or more Customers"})

[darrenj1471]

Amazing, the formula worked to count the text entries so I now have 2 new colums (C and E) which count B and D and this has worked a charm, I can now see how many customers or suppliers a location has in a numerical value so thanks so much.

I dont actually understand what you are saying about grouping them??? Sorry but thats really confusing for me as Im not sure if I need to manually enter something first or amend the look up each time?

Worst case I will simply use the filter to work out how many rows fall into each category....

Thanks again