Find Keyword Density in Rows and Columns

[MarkoX50]

Hi, I have a workbook that has 1 column (Column A) with 8000 Rows. Each Cell contains a string of words ranging from 1 word to 100 words. I am looking for a formula that can create a list of most common words (keywords) in each cell across the column. Ideally, i would like to know the most repeated words in the entire column.

Can this be done with a formula or can it only be done via VBA, if so, can someone please assist me with the code or formula.

Much Appreciated.

 

[MarkoX50]

Hi Mick, it allows seems when you think you done, some bright spark wants its easier.

Is it possible to reference a list in a new sheet for the words to exclude, instead of typing it in. I have around 100+ words which need to be excluded.
So this line would actually reference a list of words in Sheet2 column A. "NoStr = "#cat#dog#the#" ' Alter/Add words not to include here !!!!".

Let me know if this can be done.

Thanks and really appreciate your help.

 

[MickG]

Alter code as below:-

[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Sp [COLOR="Navy"]As[/COLOR] Variant, R [COLOR="Navy"]As[/COLOR] Variant, NoStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] i, j, sRay [COLOR="Navy"]As[/COLOR] Variant, Temp1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] temp2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
'[COLOR="Green"][B]##########[/B][/COLOR]
'[COLOR="Green"][B]Add this bi!!![/B][/COLOR]
[COLOR="Navy"]Dim[/COLOR] wRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
[COLOR="Navy"]Set[/COLOR] wRng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] wRng: NoStr = NoStr & "#" & Dn.Value: [COLOR="Navy"]Next[/COLOR] Dn
NoStr = NoStr & "#"
'[COLOR="Green"][B]#########[/B][/COLOR]
'[COLOR="Green"][B]Remove this bit!!![/B][/COLOR]
'[COLOR="Green"][B]NoStr ="#cat#dog#the#" ' Alter/Add words not to include here !!!![/B][/COLOR]
'[COLOR="Green"][B]################[/B][/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))

Regards Mick