Count if word within a cell occurs in paragraph

T

The Great SrH

Board Regular
Joined
Jan 16, 2015
Messages
179
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
Code: 
=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!
 
Sort by date Sort by votes
The Great SrH said:
Haha yeah fair enough.

So IF I did want it to only capture the exact word, how would I go about doing that?
Click to expand...
Well, it would come back to the same question that I asked right at the beginning:
Peter_SSs said:
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?
Click to expand...
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?
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
 
Upvote 0
The Great SrH said:
To be fair I have said I am interested to know the formula differences for all 3 outcomes ..
Click to expand...
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)

Code: 
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 />
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Help counting nonblank cells
Replies
2
Views
477
theScragg
T
R
  • Locked
  • Question
Count the number of times a particular word appears in every nth column
Replies
1
Views
353
Fluff
Fluff
R
  • Solved
Count the number of times a particular word appears in every nth column
2
Replies
12
Views
665
FormR
FormR
L
  • Question
formula that looks at several worksheets and counts up how often a word is used depending on two different cell texts
Replies
0
Views
355
libraryhelp1
L
J
  • Question
Formula to paste content of a cell from a different workbook if yet another cell contains one of several words?
Replies
2
Views
289
Judykay4444
J

Forum statistics

Threads
1,214,427
Messages
6,119,419
Members
448,895
Latest member
omarahmed1

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top