Count Most Frequent Instances in a single cell

[itfc]

Hi All,

I trying to find a formula that will count the most frequent instances of text in a cell. E.G a single cell contains the text Bob, John, Ken, Bob. The answer here would be Bob

I can do this over a range but not a single cell.

Any ideas?

Thanks

[AliGW]

Welcome to the forum.

Are you using Excel 2019 or the newer 365?

[itfc]

Hi, I'm using Office Professional Plus 2019

[sandy666]

try Power Query

source		source	Count
Bob, John, Ken, Bob		Bob	2

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[FlameRetired]

Edit: Please ignore this post. There are problems with this formula that I cannot seem to correct.Please check my next post.

In C1 in the example. This is the best and shortest formula I've been able to come up with that would work in Excel 2019. I don't think it will need to be array entered. But if it does and if you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

	A	B	C
1	Bob, John, Ken, Bob	4	Bob