Formulas for Counting and Displaying Text String Recurrences

Hello,

I have a column of text strings and blank cells and I'd like to develop cell formula #1 to display the most common string occurrence, and cell formula #2 to count the number of occurrences of a specific string in a separate cell.

Here's an example of the column of strings:

Harvard, Yale

Yale, UCLA

Berkeley

Yale

Harvard

And then the output table would look like this:

Most Common:	Formula #1 returns "Yale"
Search:	[Text Entry] = "Harvard"
# Occurrences:	Formula #2 returns 2

I've found similar formulas to count repeating text strings, but none that have been able to count string recurrences within a cell (i.e. "Harvard, Yale").

Any help would be much appreciated!

-thegivenbeing

Formula 2 would be

=COUNTIF(A:A,"*Harvard*")

Formula 1 will require a little more thought.

Is there a list of the words to include in the search for most common, or do you just want any word found in the cells?

Oh of course for Formula #2! Thanks for that.

As for Formula #1, ideally i would like it to search for any word. If that's impossible though, an alternative would be to search for a word in separate table.

Hi hemesh,

Thanks for your help. The only issue is that I need the formula to dynamically update (i.e. not be pre-defined for "Harvard, Yale"). So as my source table changes, the text displayed by the formula for "Most Common" might change.

I didn't realize this at first, but the same issue exists for Formula #2 as well. The formula jason.b75 gave doesn't count as the value entered for "Search" is updated.

I've attached a sample workbook though in case that helps.

-thegivenbeing

The formula was just an example based on what you said in post #1, with the "search" value in B1, you could use

=COUNTIF(A:A,"*"&B1&"*")

Still trying to work out formula #1, think that might need vba though, it seems to be beyond what is reasonably practical with native formula functions.

Thanks hemesh and jason.b75. That makes sense re: Formula #2.

If it seems too arduous (or near impossible) to devise the code for Formula #1, then I could also make use of VBA. It just might require some extra tweaking, given the final report structure.

See if this UDF does what you need, copy the code to the vba editor,

Please Login or Register  to view this content.

Then use the formula in the worksheet to get the most common word

=MAX_OCCURRENCE(A1:A10)

I would suggest using a suitable sized range in the formula rather than whole columns, I haven't tried with an entire column but I would imagine processing would be very slow.

In the event of a tie, the word found earliest in the list will be returned.