Finding most common words in column WITHOUT specifying what words

echrist · Sep 12, 2013

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!

mwrohr · Sep 12, 2013

to answer your question simply - yes it's possible. Whether the amount of time needed to put into it is worthwhile or affordable for your situation are different conversations.

Directionally how I would do it is to parse out each cell onto a new worksheet - put each word into a new cell, then you can do a pivot table to give you counts of the words, then sort the pivot table. There may be other ways to do it, that is just one that pops into my head up front.

Andrew Poulsom · Sep 12, 2013

Maybe this will help you:

The Spreadsheet Page Excel Downloads: Generate A Word Frequency List

echrist · Sep 12, 2013

Andrew Poulsom said:
Maybe this will help you:

The Spreadsheet Page Excel Downloads: Generate A Word Frequency List

This worked perfectly, thank you so much!

Nina81 · Jun 3, 2015

Hello, I tried using the spreadsheet Andrew posted and am running into a problem. I receive an error message when I try to run it that says "Run-time error '1004': 'MethodCreatePivotTable' of object 'PivotCache' failed". If I hit "end" or "debug" I get the same response--a new sheet appears with all of the words spread out (no more punctuation) in individual cells, but there isn't anything showing how many times each appears. I then need to go to the "run" tab and hit "continue" in order to try it again. The result is, however, always the same. Is there an update to this, or can anyone help? I'm running it on a Mac excel v 14.5.1.

A little more info in case it's helpful: this spreadsheet has multiple words in each cell, and I'd like to have a list of the words with how many times each occurs, in order of occurrence.

Thank you!

Jennifre · Jul 2, 2019

Hi, this is awesome! I am wondering if there is a way to do this, and ADD a few words found on BOTH SIDES of the the words found... would that be possible somehow? (My guess is yes, but... how?)

I hope someone sees this. THIS combined with the earlier solution posted here, would be magic! Thank you -- Mr Excel experts never fail !

mukunthaniyer · Oct 16, 2019

Andrew Poulsom said:
Maybe this will help you:

The Spreadsheet Page Excel Downloads: Generate A Word Frequency List

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

Fluff · Oct 16, 2019

That site no longer exists, so there is no uptodate link

default_name · Oct 25, 2019

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

I had the same question. But since the site no longer exists (as Fluff stated), is there anything current that we can use for this kind of problem (similar to what was in the referenced link)?

Akuini · Oct 26, 2019

default_name said:
I had the same question. But since the site no longer exists (as Fluff stated), is there anything current that we can use for this kind of problem (similar to what was in the referenced link)?

Here's a code to get words frequency.
Put the data in col A, run the code, the result is in col D:E.

Note: You need to add the reference “Microsoft Forms 2.0 Object Library” (to work with clipboard)
And this is how:
1. Open your VBA editor.
2. Click Tools > References.
3. Check the box next to “Microsoft Forms 2.0 Object Library.”

Code:

[FONT=Lucida Console][COLOR=Royalblue]Sub[/COLOR] regexWordFrequency1()
[I][COLOR=Dimgray]'word frequency[/COLOR][/I]
[I][COLOR=Dimgray]'Put the data in col A, run the code, the result is in col D:E.[/COLOR][/I]

[COLOR=Royalblue]Dim[/COLOR] regEx [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Object[/COLOR], matches [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Object[/COLOR], x [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Object[/COLOR], d [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Object[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] obj [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]New[/COLOR] DataObject
[COLOR=Royalblue]Dim[/COLOR] tx [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR], z [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR]


Range([COLOR=Darkcyan]"A1"[/COLOR], Cells(Rows.count, [COLOR=Darkcyan]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp)).Copy
obj.GetFromClipboard
tx = obj.GetText
Application.CutCopyMode = False
tx = Replace(tx, [COLOR=Darkcyan]"'"[/COLOR], [COLOR=Darkcyan]"___"[/COLOR])
    
        [COLOR=Royalblue]Set[/COLOR] regEx = CreateObject([COLOR=Darkcyan]"VBScript.RegExp"[/COLOR])
        [COLOR=Royalblue]With[/COLOR] regEx
            .[COLOR=Royalblue]Global[/COLOR] = True
            .MultiLine = True
            .IgnoreCase = True
            .pattern = [COLOR=Darkcyan]"\w+"[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]

    [COLOR=Royalblue]Set[/COLOR] d = CreateObject([COLOR=Darkcyan]"scripting.dictionary"[/COLOR])
    d.CompareMode = vbTextCompare
        
            [COLOR=Royalblue]Set[/COLOR] matches = regEx.Execute(tx)
            
            [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] x In matches
                z = [COLOR=Royalblue]CStr[/COLOR](x)
                [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] d.Exists(z) [COLOR=Royalblue]Then[/COLOR]
                    d(z) = [COLOR=Brown]1[/COLOR]
                [COLOR=Royalblue]Else[/COLOR]
                    d(z) = d(z) + [COLOR=Brown]1[/COLOR]
                [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
            [COLOR=Royalblue]Next[/COLOR]
                
[COLOR=Royalblue]If[/COLOR] d.count = [COLOR=Brown]0[/COLOR] [COLOR=Royalblue]Then[/COLOR] MsgBox [COLOR=Darkcyan]"[COLOR=Royalblue]Nothing[/COLOR] found"[/COLOR]: [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
Range([COLOR=Darkcyan]"D:E"[/COLOR]).ClearContents

[I][COLOR=Dimgray]'put the result in col D:E[/COLOR][/I]
    [COLOR=Royalblue]With[/COLOR] Range([COLOR=Darkcyan]"D1"[/COLOR]).Resize(d.count, [COLOR=Brown]2[/COLOR])
    .Cells = Application.Transpose(Array(d.Keys, d.items))
    .Replace What:=[COLOR=Darkcyan]"___"[/COLOR], Replacement:=[COLOR=Darkcyan]"'"[/COLOR], LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

Example:

Excel 2013 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: honeydew"]
[TH][/TH]
[TH]

A

[/TH]
[TH]

B

[/TH]
[TH]

C

[/TH]
[TH]

D

[/TH]
[TH]

E

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: honeydew"]

1

[/TD]
[TD]You and I, why?[/TD]
[TD][/TD]
[TD][/TD]
[TD]You[/TD]
[TD]

2