Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.