Report most common text occurrences in strings of text within a table

[VictoriaExcel]

Hi,

I have 1400+ survey responses and wish to produce a report of the most common key words in strings of text within a table of responses.

An example of a response would be:

The ceremony ran very smoothly and did not go on for too long
Nice short ceremony Decorated the venue brilliantly
The band at the rose bowl was really fun and where all the pictures could be taken (eg the 2019 sign)
The outside area of the rose bowl was lovely and added a special touch

The formula if available would produce a list of the most commonly used words - in this case it would be ceremony and rose bowl (with the exception of connecting words).

I don't know what the words are to start off with so am unable to put together a range of words to count against - this is where I have become stuck. If such a formula exists I need to make a list of any words that occur more than once (I can remove connecting words such as 'the' and 'a' etc afterwards) then I can use a count if against this identified range to determine the most frequently mentioned words.

Does such a formula or vba code exist?

Many thanks in advance,

Victoria

 

[Fluff]

You can get a list of all the words & a count of the number of times they were used, see this thread https://www.mrexcel.com/forum/excel...rching-text-strings-most-occurring-words.html

The problem comes with things like "Rose Bowl" as there is no way (that I know off) to decide if it's 2 words or a phrase.