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

2 Answers 2

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

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

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.