Counting the frequency of words within a column

I have no idea if this might be possible within Excel. I have a worksheet with a column, where in each cell there are multiple names - text strings. So, a cell might look like this: "John Doe, Karen Smith, Fancy Carol" and I want to count the frequency of the repeated words within a column. A column of mine might contain 4000 entries, each with multiple names in a cell. The problem is that I don't want to count a certain text, but I want excel to identify each single name within the cell and count and report it's frequency of repetition across the whole column and repeat this until all names in all the rows of the column has been counted and reported. Any idea if this might be possible?!

I'm adding an attachment to help clearing what I'm expecting to have - in Worksheet one, there is a column with multiple text entry rows. I want Excel to create another worksheet and report the frequency of those names one by one...

You get better help on your question if you add an (small) excel file without confidential information.

Please also add the desired (expected) result in your file.

Use this to count single name, i.e "Randy Smalley":

=SUMPRODUCT(--ISNUMBER(SEARCH("Randy Smalley",$A$1:$A$4)))

Or with B1 contain single name:

=SUMPRODUCT(--ISNUMBER(SEARCH(B1,$A$1:$A$4)))

=COUNTIF(Sheet1!$A$1:$A$4,"*"&$A2&"*")
Try this and copy towards down