Is there a way to determine the frequency of words in a column separated by comma in excel?

user_18422590 0
Viewed 91

I can count the number of times a specific word appears in an excel sheet column using "countifs"

However, is there a way to automatically show the most common words and the frequency of appearance in a column of data? For eg. in the sample image, column A has rows with different words aa, am, aq...separated by a comma. I would like to calculate the most common words and the frequency of appearance as shown by column C.

Sample image

Is there a way to achieve this in Excel?

excel
1 Answers

For O365:

=LET(α,A1:A10,β,FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(", ",,α),", ","</b><b>")&"</b></a>","//b"),γ,TRANSPOSE(UNIQUE(β)),δ,MMULT(SEQUENCE(,ROWS(β))^0,N(β=γ)),TRANSPOSE(SORTBY(CHOOSE(SEQUENCE(2),γ,δ),δ,-1)))

user_170077040
Related