excel - Frequency of Words within Cells within Range -

i have column of 50 cells. each cell contains block of text, anywhere 3-8 sentences.

id populate list of words being used , obtain frequencies entire range (a1:a50).

ive tried manipulate other codes i've found in other posts seem tailored cells contain 1 word rather multiple words.

this code found attempting use.

sub ftable() dim bigstring string, long, j long, k long dim selection range  set selection = thisworkbook.sheets("sheet1").columns("a") bigstring = "" each r in selection      bigstring = bigstring & " " & r.value next r bigstring = trim(bigstring) ary = split(bigstring, " ") dim cl collection set cl = new collection each in ary     on error resume next     cl.add a, cstr(a) next  = 1 cl.count     v = cl(i)     thisworkbook.sheets("sheet2").cells(i, "b").value = v     j = 0     each in ary         if = v j = j + 1     next     thisworkbook.sheets("sheet2").cells(i, "c") = j next end sub 

here go, dictionary best way handle think can test if dictionary contains item. post if there's don't get.

sub countwords()  dim dictionary object dim sentence() string dim arraypos integer dim lastrow, rowcounter long dim ws, destination worksheet  set ws = sheets("put source sheet name here") set destination = sheets("put destination sheet name here")  rowcounter = 2 arraypos = 0 lastrow = ws.range("a1000000").end(xlup).row  set dictionary = createobject("scripting.dictionary")  x = 2 lastrow     sentence = split(ws.cells(x, 1), " ")     y = 0 ubound(sentence)         if not dictionary.exists(sentence(y))             dictionary.add sentence(y), 1         else             dictionary.item(sentence(y)) = dictionary.item(sentence(y)) + 1         end if     next y next x  each item in dictionary     destination.cells(rowcounter, 1) = item     destination.cells(rowcounter, 2) = dictionary.item(item)     rowcounter = rowcounter + 1 next item  end sub