Frequency Count for Top 10 Words

My earlier comments below describe RegExp in Function Top10Words2. RegExp defines \w as a word metacharacter equivalent to [A-Za-z0-9_]; however, English dictionaries say words are built from letters, hyphen, and apostrophe, or [A-Za-z-']. Therefore, the following statement in Top10Words2
    RegEx.Pattern = "(\w+)" '\w is the same as [A-Za-z0-9_]
should be replaced by
    RegEx.Pattern = "([A-Za-z-']+)"
For related discussion, see my recent comment here:
https://excelribbon.tips.net/T011792_Returning_the_Final_Word.html

Re. my most recent comment below, here's another way to handle ties. In the original version of Function Top10Words2, replace these statements
    For n = 1 To k
        Result(n, 1) = Words(n - 1) 'unique word (case ignored)
        Result(n, 2) = Uniq.Item(Words(n - 1)) 'unique word count
    Next n
    Top10Words2 = Result
with the following statements:
    Dim j As Long 'initially zero
    For n = 1 To k
        If j < Uniq.Count Then
            Result(n, 1) = Uniq.Item(Words(j)) 'unique word count
            Result(n, 2) = Words(j) 'unique word (case ignored)
            j = j + 1
            Do While j < Uniq.Count
                If Uniq.Item(Words(j)) <> Result(n, 1) Then Exit Do
                Result(n, 2) = Result(n, 2) & ", " & Words(j)
                j = j + 1
            Loop
        Else
            Result(n, 1) = vbNullString
            Result(n, 2) = vbNullString
        End If
    Next n
    Top10Words2 = Result
This change combines all the words with equal frequency count into a comma separated group. Notice frequency count is now in the 1st column and words are in the 2nd. The 1st column will no longer contain duplicate count values. You might want to format the 2nd column as Wrap Text.

One issue for a list of the top 10 words is how to handle ties; i.e., what if there are more words with the same frequency as the 10th word in the list. Here's a solution for the Top10Words2 function described in my previous two comments below. Replace the final statement
    Top10Words2 = Result
with the following statements:
    If Uniq.Count > 10 Then 'add words with tied Uniq.Item
        For n = k + 1 To Uniq.Count
            If Uniq.Item(Words(n - 1)) < Result(k, 2) Then Exit For
            Result(k, 1) = Result(k, 1) & ", " & Words(n - 1)
        Next n
    End If
    Top10Words2 = Result
This change combines the 10th word in the list with a comma separated group of additional words that have the same frequency. You might want to format that cell as Wrap Text.

Sorry. I forgot to format the function. Here's a prettier version:

Function Top10Words2(Target As Variant) As Variant
    'Early binding requires VBE > Tools > References: _
        Microsoft Scripting Runtime _
        Microsoft VBScript Regular Expressions
    Dim Uniq As New Dictionary, Ignor As New Dictionary
    Dim RegEx As New RegExp, expr As Match, n As Long, k As Long
    Dim cell As Variant, word As Variant, Words As Variant, Result As Variant
    Const SKIP As String = "the,and,of,to,a,in,for,on,at,with,is,it,this," _
        & "that,as,be,by,an,or,from,was,were,are,but,not,so,if,then,than," _
        & "too,can,could,would,should,has,have,had,do,does,did,will,shall," _
        & "may,might,must,also,just,about,into,out,up,down,over,under," _
        & "again,still,only" 'consider adjusting this list
    For Each word In Split(SKIP, ",")
        Ignor.Add Key:=word, Item:=Null
    Next word
    RegEx.Global = True
    RegEx.Multiline = True
    RegEx.IgnoreCase = True
    RegEx.Pattern = "(\w+)" '\w is the same as [A-Za-z0-9_]
    For Each cell In Target
        If RegEx.Test(cell) Then
            For Each expr In RegEx.Execute(cell)
                word = LCase(CStr(expr)) 'ignore case
                If Uniq.Exists(word) Then
                    Uniq.Item(word) = Uniq.Item(word) + 1
                ElseIf Not Ignor.Exists(word) Then
                    Uniq.Add Key:=word, Item:=1
                End If
            Next expr
        End If
    Next cell
    Words = Uniq.Keys 'base-0 array
    For n = 0 To UBound(Words) - 1 'bubble-sort Uniq.Items descending
        For k = n + 1 To UBound(Words)
            If Uniq.Item(Words(k)) > Uniq.Item(Words(n)) Then
                word = Words(k)
                Words(k) = Words(n)
                Words(n) = word
            End If
        Next k
    Next n
    k = Uniq.Count
    If k > 10 Then k = 10
    ReDim Result(1 To k, 1 To 2) 'k rows, 2 columns
    For n = 1 To k
        Result(n, 1) = Words(n - 1) 'unique word (case ignored)
        Result(n, 2) = Uniq.Item(Words(n - 1)) 'unique word count
    Next n
    Top10Words2 = Result
End Function

Here's an alternate version of the Tip's macro as a user-defined function (UDF) that returns a dynamic array with 10 rows (or less) and 2 columns (word and count). Dictionary and RegExp are more efficient than Collection and Mid. Early binding requires using VB Editor's Tools menu to Reference both Microsoft Scripting Runtime and Microsoft VBScript Regular Expressions libraries. Target can be a cell range (contiguous or not) or an array. If there are no words in Target, #VALUE! error is returned. Like the Tip's macro, text is converted to lower case. This function counts words containing letters (a-z), numbers (0-9), and underscore(_); any other character is a word separator. But the Tip's macro separates words by the space character and only considers words containing letters (a-z); non-letters are simply eliminated from a word, so a hyphenated word like non-letter becomes nonletter and a word like Top10Words2 becomes topwords.

Function Top10Words2(Target As Variant) As Variant
'Early binding requires VBE > Tools > References: _
Microsoft Scripting Runtime _
Microsoft VBScript Regular Expressions
Dim Uniq As New Dictionary, Ignor As New Dictionary
Dim RegEx As New RegExp, expr As Match, n As Long, k As Long
Dim cell As Variant, word As Variant, Words As Variant, Result As Variant
Const SKIP As String = "the,and,of,to,a,in,for,on,at,with,is,it,this," _
& "that,as,be,by,an,or,from,was,were,are,but,not,so,if,then,than," _
& "too,can,could,would,should,has,have,had,do,does,did,will,shall," _
& "may,might,must,also,just,about,into,out,up,down,over,under," _
& "again,still,only" 'consider adjusting this list
For Each word In Split(SKIP, ",")
Ignor.Add Key:=word, Item:=Null
Next word
RegEx.Global = True
RegEx.Multiline = True
RegEx.IgnoreCase = True
RegEx.Pattern = "(\w+)" '\w is the same as [A-Za-z0-9_]
For Each cell In Target
If RegEx.Test(cell) Then
For Each expr In RegEx.Execute(cell)
word = LCase(CStr(expr)) 'ignore case
If Uniq.Exists(word) Then
Uniq.Item(word) = Uniq.Item(word) + 1
ElseIf Not Ignor.Exists(word) Then
Uniq.Add Key:=word, Item:=1
End If
Next expr
End If
Next cell
Words = Uniq.Keys 'base-0 array
For n = 0 To UBound(Words) - 1 'bubble-sort Uniq.Items descending
For k = n + 1 To UBound(Words)
If Uniq.Item(Words(k)) > Uniq.Item(Words(n)) Then
word = Words(k)
Words(k) = Words(n)
Words(n) = word
End If
Next k
Next n
k = Uniq.Count
If k > 10 Then k = 10
ReDim Result(1 To k, 1 To 2) 'k rows, 2 columns
For n = 1 To k
Result(n, 1) = Words(n - 1) 'unique word (case ignored)
Result(n, 2) = Uniq.Item(Words(n - 1)) 'unique word count
Next n
Top10Words2 = Result
End Function

If your version is earlier than Excel 2021, select an empty 10x2 range and use Ctrl+Shift+Enter for a CSE array formula like {=Top10Words2(A1:A999)}.