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

 

[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

Here is a modification to the above code that will make it slightly more efficient...

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

 

[liiamra]

Hi

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

=KeywordCount(B1,A1:A10)

Dear pgc01,
Many thanks. I just made the keywords range static with the $ sign, and the function seems to work fine so far. However, it is case sensitive. I wonder if it would be possible to alter it so it becomes insensitive to upper and lower letters.

Thanks again & Regards

 

[liiamra]

Here is a modification to the above code that will make it slightly more efficient...

Dear Rick,

Many thanks for your help. Actually, I have used many of your old contributions in the forum.

The code seems to work perfectly with no errors. The time it takes to execute, however, is lengthy (and Excel becomes unresponsive during the process). When I alter the sheet with any kind, it seems that the module refreshes itself and again, Excel becomes unresponsive for a short duration- after which it comes back.

Kindly note that I am using a fair-specs laptop with 8GB Rams and A8 AMD processor.

I just wonder if there is a way to make this great function more efficient; or let me put it this way, do you think I could get a faster function for the task I am interested in? please note that pgc01's solution seems to work faster, but it is case-sensitive.

Thanks again & all the best//

 

[pgc01]

I'm glad it helps

However, it is case sensitive. I wonder if it would be possible to alter it so it becomes insensitive to upper and lower letters.

In that case you just have to set it to ignore case:

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

 

[liiamra]

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.

Dear Aladin,

Thanks a lot for your reply. I have checked the limits (I am running Excel 2007, which I prefer), and it doesn't seem to tell about the whoe string or else. What I found a little relevant was just the total number of chars in a cell.

Anyways, thanks really for your help; but although your solution works perfectly for short contents, the other solutions provided by Rick & pgc01 seem to work for cells with large contents.

Thanks again & all best//

 

[Aladin Akyurek]

Dear Aladin,

Thanks a lot for your reply. I have checked the limits (I am running Excel 2007, which I prefer), and it doesn't seem to tell about the whoe string or else. What I found a little relevant was just the total number of chars in a cell.

Anyways, thanks really for your help; but although your solution works perfectly for short contents, the other solutions provided by Rick & pgc01 seem to work for cells with large contents.

Thanks again & all best//

No problem. Thanks for providing feedback. I could not establish the problem though. If the text is huge, as I also at the outset, a direct VBA approach is better for this type of problems.

 

[pgc01]

Hi

In regard to the problem of Aladin's solution, Evaluate() only accepts small strings.

Check the help or here:

Application.Evaluate Method (Excel)

 

[Aladin Akyurek]

Hi

In regard to the problem of Aladin's solution, Evaluate() only accepts small strings.

Check the help or here:

Application.Evaluate Method (Excel)

Part of the EVAL definition. Good to know. Thanks for the contribution.

 

[liiamra]

I'm glad it helps



In that case you just have to set it to ignore case:

Dear pcg01,

Many thanks for your reply. I think that my problem is solved now, and the process is fast too : ).

If there are any issues that you think I might get into using this function, I would be thankful if you inform me about them.

Again, many thanks and all best - appreciate your help.

 

[pgc01]

Aladin, you're welcome.

Liiamra, I don't see any issues and I'm glad it helped.
Remark: the only think I could say is that if you know vba you can make it even faster.
The question here is that the function creates and disposes of the object each time it runs. You could try to use the same object during different runs of the function and that would be faster. In this case, however, since it works ok for you, maybe it's not worth the trouble.