Most common words in a post, but trying to exclude conjunctions, prepositions etc. in Google Sheets

user_17677886 0
Viewed 64

TL:DR

Trying to figure out how to exclude words from a "find key words" formula in Google Sheets to ensure relevant keywords for SEO.

First question here - exciting right ! So, I have a spreadsheet with social media posts and I want to get a sense which are the most commonly used words. I have used the following formula with excellent results:

=ARRAYFORMULA((Query(TRANSPOSE(SPLIT(JOIN(" ",B7:B26), " ")&{"";""}),"select Col1, count(Col2) group by Col1 order by count(Col2) desc limit 20 label Col1 'Word', count (Col2) 'Frequency'",0)))

However, and here is the question. I want to exclude commonly used words such as "a", "the", "to", well you get the point. I have yet to figure out how to do this. Ideally, I have a separate sheet where I have an "exclusion list" of such words to be removed.

Thanks so much for taking time to help!

Best!

google-sheets-formula google-sheets
1 Answers

try adding where with regex where "A2:A" is your "list":

=ARRAYFORMULA((QUERY(TRANSPOSE(SPLIT(JOIN(" ", B7:B26), " ")&{""; ""}),
 "select Col1,count(Col2) 
  where not lower(Col1) matches '^"&TEXTJOIN("$|^", 1, LOWER(A2:A))&"$'
  group by Col1 
  order by count(Col2) desc 
  limit 20 
  label Col1'Word',count(Col2)'Frequency'", 0)))
user_56326290
Related