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).

-

I don't have the reputation to comment in the above thread, but I was able to extend JohnL's answer so that it works on multiple cells:

``````=ArrayFormula(SUM(IF(ISNUMBER(find ("search text", split(Concatenate(A1:A3), ","))),1,0)))
``````

where `A1:A3` are the cells and "search text" is the text to search.

-

``````=COUNTIF(SPLIT(A1,","),"John")
``````
-
Is this specific to certain versions of Excel? I was not aware that SPLIT works outside of VBA. – fixer1234 Feb 6 '15 at 7:28
@fixer1234: I think this only works in Google spreadsheets. I've clarified the answer. – erwaman Feb 9 '15 at 2:19

I think you probably found the best way.

An alternative:

``````=(LEN(A1)-LEN(SUBSTITUTE(A1,"John",)))/LEN("John")
``````
-
That's an interesting approach, using LEN. Will keep this strategy in mind, when considering other problems. Thanks! – johnL Jul 26 '10 at 4:28
``````=SUM(IF(ISNUMBER(FIND("John"; SPLIT(A1; ",")));1;0))