# excel / google spreadsheet: count substring occurrences within a cell

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

-

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!

-
How to do this for multiple cells? so if you would have multiple cells like A1? – Michael Trouw Feb 3 '15 at 4:41
The SPLIT function works outside of VBA? – fixer1234 Feb 6 '15 at 7:27

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
This is the answer I had to use for Google Spreadsheets. – Andrew Downes Mar 30 '15 at 14:43

Here's an approach that works in Google spreadsheets:

``````=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
Does not work for me in Google Spreadsheet. The only one I found to work on Google Spreadsheet from this page is @variant's answer. – Andrew Downes Mar 30 '15 at 14:43
Works for me: docs.google.com/spreadsheets/d/… – erwaman Mar 31 '15 at 5:31

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.

-