Count number of keywords (range) found in a text string

[liiamra]

Dear All,

I am trying to count the number of specific words contained in a specific cell. If my data were static, and the list of keywords was short, there are many solutions such as using multiple instances of the length function as proposed by @shg here.

@njimack has proposed a very easy and helpful solution here, but it serves for listing the words and not counting them.

Suppose I have the following sheet: (column C is what I am trying to achieve)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Keywords[/TD]
[TD]Comments[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]happy[/TD]
[TD]Life is beautiful, you just need to stay happy and calm. staying happy is important.[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]stay[/TD]
[TD]no matter what you do, don't get upset![/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]smile[/TD]
[TD]smile in the morning, smile in the evening, smile always[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]beautiful[/TD]
[TD]hello world, how is everyone doing? I am happy[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]upset[/TD]
[TD]don't be upset, cheer up.[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]hello[/TD]
[TD]smile smile, smile. smile, smile[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

That is, C2 will tell that B2 contained 3 of the keywords (1X beautiful, 2X happy) and so on.

Any recommendations are highly appreciated.

Thanks a lot

 

[anthonya2369]

Function wordcount(ByVal strWord As String, ByVal rngStrings As Range) As Double

Dim strTextString As String
Dim wordcounting() As String

Application.Volatile

For Each icell In rngStrings
    strTextString = strTextString & ";" & icell.Value
Next icell



wordcounting = Split(StrConv(strTextString, vbUpperCase), StrConv(strWord, vbUpperCase))

wordcount = UBound(wordcounting)

If wordcount = -1 Then wordcount = 0

End Function

reference cell would be =wordcount(a2,$B$2:$B$7) and so on

 

[Aladin Akyurek]

Dear All,

I am trying to count the number of specific words contained in a specific cell. If my data were static, and the list of keywords was short, there are many solutions such as using multiple instances of the length function as proposed by @shg here.

@njimack has proposed a very easy and helpful solution here, but it serves for listing the words and not counting them.

Suppose I have the following sheet: (column C is what I am trying to achieve)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Keywords[/TD]
[TD]Comments[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]happy[/TD]
[TD]Life is beautiful, you just need to stay happy and calm. staying happy is important.[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]stay[/TD]
[TD]no matter what you do, don't get upset![/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]smile[/TD]
[TD]smile in the morning, smile in the evening, smile always[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]beautiful[/TD]
[TD]hello world, how is everyone doing? I am happy[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]upset[/TD]
[TD]don't be upset, cheer up.[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]hello[/TD]
[TD]smile smile, smile. smile, smile[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

That is, C2 will tell that B2 contained 3 of the keywords (1X beautiful, 2X happy) and so on.

Any recommendations are highly appreciated.

Thanks a lot

Generally speaking, such tasks should be done completely in VBA if at all.

Add the following generic functions in VBA to your workbook, using Alt+F11...

Public Function EVAL(theInput As Variant) As Variant
'
' if UDF evaluate the input string as though it was on this sheet
' else evaluate for activesheet
'
Dim vEval As Variant
Application.Volatile
On Error GoTo funcfail
If Not IsEmpty(theInput) Then
If TypeOf Application.Caller.Parent Is Worksheet Then
vEval = Application.Caller.Parent.Evaluate(CStr(theInput))
Else
vEval = Application.Evaluate(CStr(theInput))
End If
If IsError(vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Exit Function
funcfail:
EVAL = CVErr(xlErrNA)
End Function

Function RemovePunctuation(r As String) As String
' Adapted by Scott Huish, 8/23/2013
With CreateObject("vbscript.regexp")
.Pattern = "[^A-Za-z0-9\ ']"
.Global = True
RemovePunctuation = .Replace(r, "")
End With
End Function

Now invoke...

C2, copied down:

=SUMPRODUCT((ISNUMBER(MATCH(eval("{"&CHAR(34)&
   SUBSTITUTE(removepunctuation(B2)," ",CHAR(34)&","&CHAR(34))&CHAR(34)&"}"),
   $A$2:$A$7,0))+0))

 

[liiamra]

Dear Anthony,

Many thanks for your assistance.

Actually, I thought I could use the method proposed by @njimack (link in the first post), and then use the count words function, but his function doesn't return repeated words and has some additional flows.

Your method works very well - many thanks, but what I need is exactly the opposite. That is, I need the "range" to be the keywords. ex: I need to know how many times the keyword(s) were mentioned in a given string.

Thanks again - really appreciate your help.
All Best//

 

[liiamra]

Dear Aladin,

Many many thanks for your help. Your function is really great, however I have run into one issue.

First, the function can handle really large number of keywords, i.e +2000. This is a very big advantage.

However, the function returns "0" when the string contains more than a certain number of characters. I couldn't really determine this number as it depends on the type of the character.

For example, if you try the following text, it wouldn't work. But if you remove the letter "v" from the end, it would. I really need it to work as the strings (text in cell) are large.

heart- smile in the morning, smile in the evenings, always == 5 I always believed in numbers and science, physics and metaphysics. I always believed that life will turn good. I've believed v

I wonder also if you can think of any other limitations that I might run into.

Again, thank you very much Aladin (really appreciate your assistance) and all best//

 

[Aladin Akyurek]

Dear Aladin,

Many many thanks for your help. Your function is really great, however I have run into one issue.

First, the function can handle really large number of keywords, i.e +2000. This is a very big advantage.

However, the function returns "0" when the string contains more than a certain number of characters. I couldn't really determine this number as it depends on the type of the character.

For example, if you try the following text, it wouldn't work. But if you remove the letter "v" from the end, it would. I really need it to work as the strings (text in cell) are large.




I wonder also if you can think of any other limitations that I might run into.

Again, thank you very much Aladin (really appreciate your assistance) and all best//

I don't seem to have any problem with that string, i.e.:

heart- smile in the morning, smile in the evenings, always == 5 I always believed in numbers and science, physics and metaphysics. I always believed that life will turn good. I've believed v

I get a count of 2, related to "smile".

 

[liiamra]

Thanks Aladin for you reply,

My bad- I must have altered the contents before I posted. Please just add more text and you will see what I mean. I added the word "completely" and it returns "0" now. Please note that it just depends on the number of characters.

heart- smile in the morning, smile in the evenings, always == 5 I always believed in numbers and science, physics and metaphysics. I always believed that life will turn good. I've believed completely

Thanks again & all best//

 

[Aladin Akyurek]

Thanks Aladin for you reply,

My bad- I must have altered the contents before I posted. Please just add more text and you will see what I mean. I added the word "completely" and it returns "0" now. Please note that it just depends on the number of characters.



Thanks again & all best//

The whoe string is 192 chars long and counts 31 words. The latter is also the number of array elements the formula works with (see the EVAL bit). May I ask you to check your Excel's help for "limits" in oder to assess the stuation.

 

[Rick Rothstein]

Here is a UDF (user defined function) that you can try. It takes two arguments, the first argument is the text string that you want look for the words in and the second is the range containing your list of words. So for the example you posted in Message #1, you would put this formula in cell C2 and copy it down to C7...

=WordListCount(B2,A$2:A$7)

Here is the code for the UDF...

Function WordListCount(TextToSearch As String, WordList As Range) As Long
  Dim WL As Variant, R As Long, C As Long, X As Long
  WL = WordList
  For R = 1 To UBound(WL)
    For C = 1 To UBound(WL, 2)
      For X = 1 To Len(TextToSearch)
        If Mid(" " & Ucase(TextToSearch) & " ", X, Len(WL(R, C)) + 2) _
                Like "[!0-9A-Z]" & Ucase(WL(R, C)) & "[!0-9A-Z]" Then
          WordListCount = WordListCount + 1
        End If
      Next
    Next
  Next
End Function

 

[pgc01]

Hi

Another option, assuming the keywords are in a contiguous vertical range. Ex.:

=KeywordCount(B1,A1:A10)

Function KeywordCount(sTextToSearch As String, rKeywords As Range) As Long
  
With CreateObject("VBScript.RegExp")
    .Pattern = "\b(" & Join(Application.Transpose(rKeywords), "|") & ")\b"
    .Global = True
    KeywordCount = .Execute(sTextToSearch).Count
End With
End Function