excel / google spreadsheet: count substring occurrences within a cell

Question

I've been scratching my head trying to figure out a way to count the number of occurrences of a substring within a cell.

For example:

|   |         A         |
| 1 |John,John,Tom,David|

What formula would I use if I want to return the number of occurrences of "John" in cell A1? (it should return 2).

Answer 1

Wow, after searching around for a month on this problem, I stumbled upon the answer after posting this question. Here is what I came up with in case anyone else has this problem.

=SUM(IF(ISNUMBER(FIND("John"; SPLIT(A1; ",")));1;0))

This is an array formula so will need to be entered using [CTRL]+[SHIFT]+[ENTER]

If anyone thinks of a better way to solve this problem, please let me know!