Thank you, this is helpful, however it gets more complicated now.

I have a series of words in each cell in a column AN. The cells go from
AN16-AN200.
Each cell has a variable amount of words that are separated by commas.
I need to know how many times a word appears in that cell and I need to put
that number in another cell.
ex.
AN
16-COMP1,HNF,COUPTF
MAZR,COUPTF
E2F,MAZ,E2F-1,Pax-6,Pax-3,myogenin/NF-1,KROX,COMP1,E2F-1,Sp3,Muscle

In another column AX16, I need to insert the number of times COMP1 occurs. I
also need to be able to scroll the formula down to all of the cells that
might have COMP1 in them and put in the number in the corresponding AX
column.

Thank you!





"Jacob Skaria" wrote:

From the below example; it looks like the names are separated by a comma.

=(LEN(A3)-LEN(SUBSTITUTE(A3,"Jon" & ",",",")))/LEN("Jon")

with Jon in B1 and the text string in A3...
=(LEN(A3)-LEN(SUBSTITUTE(A3,B1 & ",",",")))/LEN(B1)


If this post helps click Yes
---------------
Jacob Skaria


"Chris the researcher" wrote:

I need to count the number of times a word shows up in a given cell for which
the number will then show up below the name of the word.

ex. B1
[Jon]
A3 ? How many times did
Jon appear?
[Jon, Kate, Jon, Lilly]


I want excel to put in a number where the ? is.

Please help!