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


New Member
Nov 15, 2018

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,


Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...