Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

3 Answers 3

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 at 4:41
The SPLIT function works outside of VBA? –  fixer1234 Feb 6 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

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 at 7:28
@fixer1234: I think this only works in Google spreadsheets. I've clarified the answer. –  erwaman Feb 9 at 2:19

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.