Menu
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!
643
Select Lookup option from the Formula Type drop down list; Then choose Find most common value from the Choose a fromula list box; In the right Arguments input section, select a list of cells that you want to extract the most common value beside the Range.
Let's say that you want to see how many words within a specific paragraph, Google Docs has made it simple to access this information as well. You will start by highlighting the group of text you'd like to measure, then repeat step one and two from above by clicking on “Tools” and then selecting “Word count.”
To find the frequency of a word in a text, use the function FREQUENCY() in Google Sheets. The function takes two arguments: the text you want to analyze and the word you want to find the frequency of. The function returns the value 2, which means that the word "the" appears 2 times in the text "The Cat in the Hat".
You can use the =UNIQUE() and =COUNTIF() functions to count the number of occurrences of different values in a column in Google Sheets.
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))
112
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
