Count if word within a cell occurs in paragraph

[The Great SrH]

Hi all,

I'm looking for some help with a formula ideally (but if VBA is only option I'll accept that).


Basically I have a worksheet with data (called Data) in (Columns E to K contain comments boxes which may have lots of words in), and then another worksheet (called Words) with a list of 60 words.


I wan't to put a formula in the cell next to the word which will count the number of times the word in column A is used from Data Worksheet in columns E to K.

Something similar to

=COUNTIF(Data!E:K,A3)

The only issue is if the cell (A3) in Words worksheet says "Fair". This formula is searching the Data tab and returning cells which simply say "Fair".


I need it count the amount of times the word Fair is used across all columns (or even just Column E if thats easier).


It may be that a cell contains a lot of words like:
"Fantastic manager, she is always fair and will have an acceptable reason if she cannot do something, otherwise she'll go out of her way for everyone."


Thanks in advance!

 

[Peter_SSs]

Haha yeah fair enough.

So IF I did want it to only capture the exact word, how would I go about doing that?

Well, it would come back to the same question that I asked right at the beginning:

If one of the words to check for is "fair" and a cell contains "John thinks it is fair but I don't think it is fair & Sam is fairly tired" should the count be 1, 2 or 3?

We've now determined that the answer would not be 3 if checking for exact words, but that still leaves two possible answers: 1 and 2. Which should it be?

 

[The Great SrH]

To be fair I have said I am interested to know the formula differences for all 3 outcomes, and that’s still the case.

Could you please provide support with the outcome being 1 & 2? This will help with my learning

 

[Peter_SSs]

To be fair I have said I am interested to know the formula differences for all 3 outcomes ..

That's true, but I've been involved in a number of threads over the years that have started out in that fashion and turned into a "teach me everything about Excel for free in 2 days". So I tend to be a bit wary of that sort of thing.

OK, so the 1 v 2 issue is that 1 is just counting the cells that contain the word of interest, whereas 2 is counting the actual words.

My examples below are looking at this sample data, with interest in the words "cat" and "dog".

<b>Data</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:275px;" /><col style="width:199px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >E</td><td >F</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">One cat is black and the other cat is grey</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">Under the tree</td><td style="font-size:10pt; ">Catch that ball</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">Dog</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; "></td><td style="font-size:10pt; ">No dogs here</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Bill was scating about the cat</td></tr></table> <br /><br />
Columns B:E below contain standard worksheet formulas for the counts.
The D2 formula requires the TEXTJOIN function which is only available in recent Excel versions but is faster to calculate than the E2 formula - provided the data is not huge.

Columns G:H employ a user-defined function through vba. It has an optional 3rd argument to change from counting cells to counting words. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function CountWords(r As Range, sWord As String, Optional CountAll As Boolean) As Long
  Static RX As Object
  Dim a As Variant, itm As Variant
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.IgnoreCase = True
  End If
  RX.Pattern = "\b" & sWord & "\b"
  a = r.Value
  For Each itm In a
    If RX.test(itm) Then CountWords = CountWords + IIf(CountAll, RX.Execute(itm).Count, 1)
  Next itm
End Function

Note that the different methods may produce different results if your text contains punctuation, particularly the standard worksheet functions, which would not count "cat" in My cat, Tom, is black.

<b>Words</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:49px;" /><col style="width:48px;" /><col style="width:20px;" /><col style="width:51px;" /><col style="width:51px;" /><col style="width:20px;" /><col style="width:59px;" /><col style="width:56px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">Cells</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">Words</td><td style="font-size:10pt; text-align:right; ">Words</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">Cells</td><td style="font-size:10pt; text-align:right; ">Words</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">Cat</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; text-align:right; ">3</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">Dog</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">1</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=SUMPRODUCT(--<span style=' color:008000; '>(ISNUMBER<span style=' color:#0000ff; '>(SEARCH<span style=' color:#ff0000; '>(" "&A2&" "," "&Data!E$1:K$100&" ")</span>)</span>)</span>)</td></tr><tr><td >D2</td><td >=(LEN<span style=' color:008000; '>(" "&SUBSTITUTE<span style=' color:#0000ff; '>(TEXTJOIN<span style=' color:#ff0000; '>(" ",TRUE,Data!E$1:K$100)</span>," ","  ")</span>&" ")</span>-LEN<span style=' color:008000; '>(SUBSTITUTE<span style=' color:#0000ff; '>(" "&SUBSTITUTE<span style=' color:#ff0000; '>(UPPER<span style=' color:#804000; '>(TEXTJOIN<span style=' color:#ff7837; '>(" ",TRUE,Data!E$1:K$100)</span>)</span>," ","  ")</span>&" "," "&UPPER<span style=' color:#ff0000; '>(A2)</span>&" ","")</span>)</span>)/(LEN<span style=' color:008000; '>(A2)</span>+2)</td></tr><tr><td >E2</td><td >=SUMPRODUCT(LEN<span style=' color:008000; '>(" "&SUBSTITUTE<span style=' color:#0000ff; '>(Data!E$1:K$100," ","  ")</span>&" ")</span>-LEN<span style=' color:008000; '>(SUBSTITUTE<span style=' color:#0000ff; '>(" "&SUBSTITUTE<span style=' color:#ff0000; '>(UPPER<span style=' color:#804000; '>(Data!E$1:K$100)</span>," ","  ")</span>&" "," "&UPPER<span style=' color:#ff0000; '>(A2)</span>&" ","")</span>)</span>)/(LEN<span style=' color:008000; '>(A2)</span>+2)</td></tr><tr><td >G2</td><td >=CountWords(Data!E$1:K$100,A2)</td></tr><tr><td >H2</td><td >=CountWords(Data!E$1:K$100,A2,TRUE)</td></tr></table></td></tr></table> <br /><br />