Count substring occurrences within a cell

How can I 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 (=2)?

Solution 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!

Solution 2:

Here's an approach that works in Google spreadsheets:

=COUNTIF(SPLIT(A1,","),"John")