Finding most common words in column WITHOUT specifying what words

[echrist]

Hey everyone. We're doing some restructuring of our programs at my organization, and I'm trying to make a list of what is considered most important to our clients based on their feedback. We enter their questionnaire responses into Excel regularly, but there are thousands of responses. I'm trying to make a list of what words appear most frequently without going through one by one and making a list of keywords.
Without keywords, I haven't been able to find anything on the subject. As an additional hurdle, the responses are written in sentence form (there will be ~10-15 words in each response).
For example:

I want to eat more pizza.
Make your program longer.
I wish we could eat more meals.

Obviously these are made up, but the hope is that "I," "eat," etc. are returned at the top of a list of most used words.
Is this possible?
Thanks in advance!

 

[Akuini]

Is there a way to edit the code so that rather than searching for single words, it looks for common strings of 2 or 3 (or more?) words? This way you could quickly analyze to see if there are common sentences, or part sentences, cropping up, rather than just single words.

I think this is what you want:
count-word-and-random-phrase-frequency

Please read post #13 & #19

If that is what you want & you need more help then please reply in that thread.

 

[Iron_ivan4]

Hi, the link is not opening. Can you please share a latest reference for this one, please? Thanks much!

Pretty late to this but try this link its very helpful just apply the steps to your sheet.

Find the Most Repeated Word in a data set using Power Query - XL n CAD

How to find the most repeated word or text string in a data set using Power Query is explained in this blog post

xlncad.com

 

[Akuini]

I've written a macro to generate word & phrase frequency with more flexible criteria to use, here:

VBA Macro to create Word & Phrase Frequency

This macro generates word & phrase frequency. You can set the number of words contained in a phrase as needed, in this part: Const sNumber As String = "1,2,3" You can also set what characters should be considered as word characters, in this...

www.mrexcel.com