Is this possible?? Find most commonly occurring words in a cell

With VBA, almost anything is possible.


Copy the code below into a standard codemodule, and use it like

=TopWords(B2)

to return just the list of words like

a in an or to on at of and if you

or to return fewer than 10

=TopWords(B2,5)
a in an or to

or to a list showing the counts, use it like this

=TopWords(B2,,TRUE)

which will return

190: a 31: in 28: an 27: or 25: to 24: on 20: at 18: of 16: and 15: if you

And if you want to only look at longer words, use it like

=TopWords(B2,,TRUE, 4)

which will return, for example, only words that are 4 characters or longer

12: test 11: your 10: vacuum 7: window roof with 6: fork 4: attached which valve used hold

Please Login or Register  to view this content.

Hi,

Based on a string in A1, first go to Name Manager (Formulas tab) and create the following, making sure that the active cell in the worksheet at the time you do so is somewhere in row 1 (or, if the string in question is not in row 1, than in whatever row that string does happen to be):

Name: Arry1
Refers to: =ROW(INDIRECT("1:"&1+LEN($A1)-LEN(SUBSTITUTE($A1," ",""))))

Name: Arry2
Refers to: =TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),LEN($A1)*(Arry1-1)+1,LEN($A1)))

Name: Arry3
Refers to: =LEN($A1)-LEN(SUBSTITUTE($A1,Arry2,""))

Exit Name Manager.

Enter this array formula** in B1:

=SUM((0+(IF(FREQUENCY(MATCH(Arry2,Arry2,0),Arry1),Arry3,0)>=LARGE(IF(FREQUENCY(MATCH(Arry2,Arry2,0),Arry1),Arry3),10))))

Then this array formula** in C1:

=IF(COLUMNS($A:A)>$B$1,"",INDEX(Arry2,MATCH(LARGE(IF(FREQUENCY(MATCH(Arry2,Arry2,0),Arry1),Arry3+Arry1/10^9),COLUMNS($A:A)),Arry3+Arry1/10^9,0)))

Copy this formula to the right (though obviously not the one in B1) until you start to get blanks for the results.

It can also be copied down to give similar results for strings in A2, A3, etc.

Note that this solution depends on, as you say, the individual words in that string being separated by a single space precisely.

Also, you don't mention what should be the result in the case that there are less than 10 unique words within the string, so no accounting for this has been made (the formulas will currently error).

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Please see the attache sdheet, this is an array formula and required Ctrl Shift Enter to submit

Top 10 most freq numbers.xlsx

Originally Posted by TheCman81

Please see the attache sdheet, this is an array formula and required Ctrl Shift Enter to submit

Attachment 341951

I hope I didn't misunderstand the question, then? I thought we were talking about entries in a single cell?

Regards

Originally Posted by XOR LX

I hope I didn't misunderstand the question, then? I thought we were talking about entries in a single cell?

Regards

I missed the part were it said cell.