Google Docs spreadsheet formula for most frequent

Question

I wonder if there is a formula in Google Docs Spreadsheet which could identify and display (for example in column D) the most frequent (key)words in a spreadsheet?

Let's say that I have a column (Column B) full of tweets (see example image) and I would like to find top keywords in the column B and display them in column D. Is there a way to do that?

Thank you!

Answer 1

To return the top 10 individual words in column B, with their frequency, try:

=ArrayFormula(QUERY(TRANSPOSE(SPLIT(JOIN(" ";B3:B);" ")&{"";""});"select Col1, count(Col2) group by Col1 order by count(Col2) desc limit 10 label Col1 'Word', count(Col2) 'Frequency'";0))