Count Most Frequent Instances in a single cell

Welcome to the forum.

Are you using Excel 2019 or the newer 365?

try Power Query

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

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitR = Table.ExpandListColumn(Table.TransformColumns(Source, {{"source", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "source"),
    Trim = Table.TransformColumns(SplitR,{{"source", Text.Trim, type text}}),
    Grp = Table.Group(Trim, {"source"}, {{"Count", each Table.RowCount(_), type number}}),
    Desc = Table.Sort(Grp,{{"Count", Order.Descending}}),
    FirstN = Table.FirstN(Desc,1)
in
    FirstN

or

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    List = Table.AddColumn(Source, "Name", each Text.Split([source],",")),
    Trim = Table.TransformColumns(Table.ExpandListColumn(List, "Name"),{{"Name", Text.Trim, type text}}),
    Grp = Table.Group(Trim, {"Name"}, {{"Count", each Table.RowCount(_), type number}}),
    FirstN = Table.FirstN(Table.Sort(Grp,{{"Count", Order.Descending}}),1)
in
    FirstN

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.

Formula:

=IFERROR(INDEX(TRIM(MID(SUBSTITUTE(A1,", ",REPT(" ",20)),(ROW(INDIRECT("1:"&B1))-1)*LEN(A1)+1,LEN(A1))),IF(COUNT(MATCH("*"&TRIM(MID(SUBSTITUTE(A1,", ",REPT(" ",20)),(ROW(INDIRECT("1:"&B1))-1)*LEN(A1)+1,LEN(A1))),A1,0))>1,MATCH(1,MATCH("*"&TRIM(MID(SUBSTITUTE(A1,", ",REPT(" ",20)),(ROW(INDIRECT("1:"&B1))-1)*LEN(A1)+1,LEN(A1))),A1,0),0),"")),"No Dups")

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