Searching Text Strings for Most Occurring Words

[ijhoeq]

Hello,

I am wondering if anyone knows a formula to search for commonly used words in an excel column. I have data where one column is written text filled in by someone and I would like to search all of the columns for the most commonly used words. For example, several cells in column A contain phrases such as "leaking fluid from seal", "fluid loss", etc. So it would be helpful to see how many entries contain the word fluid. I appreciate any help!

Thanks!

 

[Fluff]

How about

Sub ijhoeq()
   Dim Ary As Variant, Sp As Variant
   Dim i As Long, j As Long
   
   With Sheets("Sheet1")
      Ary = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value2
   End With
   With CreateObject("scripting.dictionary")
      For i = 1 To UBound(Ary)
         Sp = Split(Ary(i, 1))
         For j = 0 To UBound(Sp)
            .Item(Sp(j)) = .Item(Sp(j)) + 1
         Next j
      Next i
      Sheets("Sheet2").Range("A1").Resize(.Count, 2).Value = Application.Transpose(Array(.keys, .items))
   End With
End Sub

Change sheet names & ranges to suit.
This will list all words used & how many times.

 

[ijhoeq]

Thank you very much! It works great!

 

[Fluff]

You're welcome & thanks for the feedback