So I have a vertical range of a few words, and I have in a cell a sentence in a form of a string. I was wondering how I could write some code in order to check for the number of occurrences of the range of words in the string. Doesn’t have to be consecutive. So for example, a1:a3 could be the words apple pear orange, and the string could be "I love pear and apple!" and the function would output 2. I have some code written down here but it only works when the search is with a string, not with a range.
Function WordListCount(TextString As String, rng As String) As Integer TextString = LCase(TextString) Dim Result() As String Dim Result2() As String Dim Count As Integer Result = Split(TextString, " ") Result2 = Split(rng, " ") Count = UBound(Result()) Dim k As Integer Dim i As Integer Dim repeat As Integer repeat = 0 For i = LBound(Result) To UBound(Result) For k = LBound(Result2) To UBound(Result2) If StrComp(Result(i), Result2(k)) = 0 Then repeat = repeat + 1 End If Next k Next i WordListCount = repeat End Function
Since this code works if what’re you’re searching for is a string instead of a range, how would I be able to convert a range to a string? preferably separated by a space?
When splitting words in a general sentence structure, you’ll likely want to split not just on whitespace but also on punctuation. An easy way of doing this is to use Regular expressions, so add a reference to the library:
Tools -> References -> Microsoft VBScript Regular Expression #
From there, you can split the text string into words and match them against your range:
Function WordListCount(TextString As String, rng As Range) As Integer Dim Rex, Matches, Count WordListCount = 0 Set Rex = New RegExp Rex.Pattern = "\w+" Rex.Global = True Set Matches = Rex.Execute(TextString) For Each Match In Matches For Each Cell In rng If (StrComp(Match.Value, Cell.Value, vbTextCompare) = 0) Then WordListCount = WordListCount + 1 ' Found word Exit For End If Next Next End Function
You can refer to the range directly, it doesn’t ned to be converted to a string:
Debug.Print WordListCount("I love pear and apple!", Range("A1:A3"))
Answered By – Mike D Sutton
Answer Checked By – Senaida (BugsFixing Volunteer)