# How to retrieve all occurences of a particular value within a string?

I'm looking at an excel work-book with potential definitions for a column(upto 135) referenced from an adjacent sheet.

E.g. Sheet 1: Col C (values 0-134 defined in Sheet 2 )

E.g. 0,1,8 Each row in Col C Sheet 1 may have any combination of the values separated by commas.

Imagine a workbook with two sheets named 'Student', and 'Team names' Each row in the sheet 'Team names' lists the ID of a team. A student may play on more than one team.

Say there are 3 teams they would appear on Sheet 'Team names' as

A1=10

A2 = 20

A3=30.

Say now that there are 4 students whose roll.no appears on Sheet 'Student' as A1 = 13, A2=27, A3=43, A4=53.

Roll.No 13 Col B 10, 20, 30

Roll.No 27 Col B 30, 10

Roll.No 53 Col B 20

Roll.No 43 Col B 30, 20

The intent here is to setup a formula such that in the sheet 'Team names'

10 Col B 2

20 Col B 3

30 Col B 3

Sheet 2 must maintain statistics of the occurence of each value. This is done using COUNTIF. The issue here is that COUNTIF doesn't handle an embedded value too well. When so done, the reference to '1' in the above example won't appear.

How can this be done without resorting to a sub-routine?

-
You mean without seperating the comma separated values into individual cells? – Paul Apr 5 '12 at 5:16

If you have the list of values in sheet2 in A2 down try this formula for a count in B2

`=SUMPRODUCT(ISNUMBER(FIND(","&A2&",",","&Sheet1!C\$2:C\$1000&","))+0)`

Copy formula down column

Assumes up to 1000 rows of data in sheet1, adjust as required

Updated

The above assumed that the data was separated by commas only (no spaces) but given your example, assuming the separators are [comma][space] together then try this revised version given your comments

`=SUMPRODUCT(ISNUMBER(FIND(" "&A1&","," "&students!B\$1:B\$4&","))+0)`

-
Let me give it a shot (+: Thank you!! – Everyone Apr 5 '12 at 17:49
Definitely on the right track. I took the liberty of assuming you'll be able to help me iron it out a little, and updated the question to make it clearer. – Everyone Apr 9 '12 at 19:58
@Everyone - I updated my answer to accommodate your changes – barry houdini Apr 9 '12 at 22:24