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

Here's an approach that works in Google spreadsheets:

=COUNTIF(SPLIT(A1,","),"John")
share|improve this answer
1  
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
    
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 yesterday
    
Works for me: docs.google.com/spreadsheets/d/… –  erwaman 15 hours ago

I think you probably found the best way.

An alternative:

=(LEN(A1)-LEN(SUBSTITUTE(A1,"John",)))/LEN("John")
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 yesterday
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

Your Answer

 
discard

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.