Counting number of occurances of a word

  • Thread starter Thread starter Chris the researcher
  • Start date Start date
C

Chris the researcher

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!
 
[Jon, Kate, Jon, Lilly]
Click to expand...

Do the brackets appear in the cell?

Are the names always separated by commas?

--
Biff
Microsoft Excel MVP


Chris the researcher said:
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!
Click to expand...
 
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
 
With string in A3 and name to count in B1, try this:

=(LEN(A3)-LEN(SUBSTITUTE(UPPER(A3),UPPER(B1),"")))/LEN(B1)
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


"Chris the researcher" <Chris the (e-mail address removed)>
wrote in message 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!
 
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 said:
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 said:
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!
Click to expand...
Click to expand...
 
It's not really more complicated.
Just adjust your cell addresses.

With the "word" you're looking to count entered in B1 (COMP1), enter this
formula in AX16:

=(LEN(AN16)-LEN(SUBSTITUTE(UPPER(AN16),UPPER(B$1),"")))/LEN(B$1)

And copy down as needed.

The inclusion of the Upper() function allows you to use "comp1" in B1, and
still count any combination of upper and lower case characters in your
Column AN, as long as they spell out "comp1".
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



Chris the researcher said:
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 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top