counting words in Excel

[rrupp]

I have a user who has imported survey results into Excel. He wants to do a
word count for each possible word. So he wants to know that the word "the"
appears 145 times and the word "chicken" appears 345 times. Is there an easy
way to do this. Very odd question, I know and I cannot figure out a way to
do it. I know how to count a specific word but not every word possiblity that
appears in the cell.

thanks for your time.

 

[Mike H]

Hi,

This isn't clear what you want so we'll start with this.

=COUNTIF(A1:A30,"The")

Mike

 

[rrupp]

Sorry if this is hard to understand. I do know how to do the COUNTIF, for a
specific word. But, he has cells that he wants to know how many times each
word appears in the cell for every single word. So, if there is a cell with
75 words, he wants to know how many times each and every word appears in that
cell.

Does that clarify ?

 

[Mike H]

Hi,

Try this which isn't case sensitive

=SUMPRODUCT((LEN(A1:A20)-LEN(SUBSTITUTE(UPPER(A1:A20),"THE","")))/LEN("THE"))

or this for case sensitive

=SUMPRODUCT((LEN(A1:A20)-LEN(SUBSTITUTE(A1:A20,"The","")))/LEN("The"))

Mike