Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
up vote 3 down vote accepted

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!

share|improve this answer
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:

share|improve this answer
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:

share|improve this answer
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:… – 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.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .