find_word_frequency.vb

Sub WordCount()
    Dim Rng As Range, Dn As Range
    Dim oMax As Double
    Dim K As Variant
    Dim Msg As String
    Dim vWords As Variant
    Dim myWord As Variant
    Dim counter As Integer, WordCount As Integer
    
    Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    With CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
        For Each Dn In Rng
            vWords = Split(Replace(Replace(Dn.Text, ",", " "), "-", " "), " ")
            For Each myWord In vWords
                If myWord <> "" Then
                    If Not .Exists(myWord) Then
                        .Add myWord, 1
                        WordCount = WordCount + 1
                    Else
                        .Item(myWord) = .Item(myWord) + 1
                    End If
                End If
            Next
        Next
        counter = 1
        For Each K In .keys
            If Application.CountIf(Range("B1:B" & counter), K) = 0 Then
                Cells(counter, 2) = K
                Cells(counter, 3) = .Item(K)
                counter = counter + 1
            End If
        Next K
    End With
End Sub