Solved: Count occurrence or particular keyword within Excel

[daemonskin1]

Hello,

Wondering if someone would be kind to point me in the right direction and/or help me with:

How to display exact count of particular keywords (words that I would like to specify) and search against the worksheet. Column that needs to be searched contains a lot of characters (words) per cell. (can be 100 plus words). Count result should display data next to its appropriate cell.



how to display data how (result would be 2 if I specify search keyword "how")

how to how data how how (result would be 4 if I specify search keyword "how")

to search data for (result would be 0...)

--------

Thank a lot!

[daemonskin1]

Example worksheet with what I would like to acomplish is attached!

:banghead:

[TonyJollans]

Hi daemonskin1,

Welcome to VBAX!

The difficulty with this is the definition of a word. Excel really isn't geared to text processing. Finding the character string "how" is easy enough but distinguishing it from, say, "show" or "howl" is quite a lot harder.

If it helps, to find the count of strings (delimited anyhow) ..

(in your example) add this formula to D2 and copy down:

=(LEN(A2)-LEN(SUBSTITUTE(A2,D$1,"")))/LEN(D$1)

Then in D1, enter your search term, e.g. how

[malik641]

Pretty nice Tony :thumb

How could you make it to not be case-sensitive??

[TonyJollans]

Hi Joseph,

This should do it ...

=(LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),UPPER(D$1),"")))/LEN(D$1)

[malik641]

Awesome :thumb

Nice Tony :clap:

[daemonskin1]

Very nice Solution TonnyJolans! Thank you kindly for the quick response. : )

Since I was previously looking at the "FindWord" solution (as posted on the kb) I would like as well to enhance that code to automate this feature as well.

That is, to automatically populate column C (within FindWord tab) with the count of words occurrences as stated on "Search Criteria Input" form.

[daemonskin1]

In the meantime let me mark this thread solved! :thumb