Counting Word Frequencies in Excel Rows

Question

Suppose I have a column of arbitrary length, where each cell contains a line of text. Is there a way to determine which words most often appear in a column (not knowing in advance which words to check), and then arrange these words along with their frequencies in a two-column table? Would VBA be better for this task?

As an example, a cell may contain the string "This is a string, and the number of characters in this string is → 0". (intentional errors)

Answer 1

Select part of column A and run this small macro (the table will be placed in columns. B and C :

Sub Ftable()
    Dim BigString As String, I As Long, J As Long, K As Long
    BigString = ""

' , "", "" " ". "!" .. , , ' . : "". "". "" "" . " ?

    For Each r In Selection 
          BigString = BigString & " " & r.Value
    Next r
    BigString = Trim(BigString)
    ary = Split(BigString, " ")
    Dim cl As Collection
    Set cl = New Collection
    For Each a In ary
        On Error Resume Next
        cl.Add a, CStr(a)
    Next a

    For I = 1 To cl.Count
        v = cl(I)
        Cells(I, "B").Value = v
        J = 0
        For Each a In ary
            If a = v Then J = J + 1
        Next a
        Cells(I, "C") = J
    Next I

End Sub

Answer 2

:

, :

, , Top 5, 10 .. . , .:)

Answer 3

Google :

index((Transpose(ArrayFormula(QUERY(TRANSPOSE(SPLIT(JOIN(" ",$B$2)," ")&{"";""}),"select Col1, count(Col2) group by Col1 order by count(Col2) desc limit 20 label Col1 'Word', count(Col2) 'Frequency'",0)))),1,$A6+1)&":"&index((Transpose(ArrayFormula(QUERY(TRANSPOSE(SPLIT(JOIN(" ",$B$2)," ")&{"";""}),"select Col1, count(Col2) group by Col1 order by count(Col2) desc limit 20 label Col1 'Word', count(Col2) 'Frequency'",0)))),2,$A6+1)

$B $2

$ A6 = 1 will give you the most commonly used word

$ A6 = 2 will give you the second most used word, etc.

Set to make the 20 most common. If you want more, increase the limit to what you want.