# Count substring occurrences within a cell

How can I 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 (=2)?

``````=COUNTIF(SPLIT(A1,","),"John")
``````
• Is this specific to certain versions of Excel? I was not aware that SPLIT works outside of VBA. Feb 6, 2015 at 7:28
• @fixer1234: I think this only works in Google spreadsheets. I've clarified the answer. Feb 9, 2015 at 2:19

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!

• How to do this for multiple cells? so if you would have multiple cells like A1? Feb 3, 2015 at 4:41
• The SPLIT function works outside of VBA? Feb 6, 2015 at 7:27
• Dosn't work in Excel 2013 saying "that function (i.e. `SPLIT`) is not valid". And the parameters in the functions should be separated by `,` not `;`. Jun 29, 2018 at 2:14

I think you probably found the best way.

An alternative:

``````=(LEN(A1)-LEN(SUBSTITUTE(A1,"John",)))/LEN("John")
``````
• That's an interesting approach, using LEN. Will keep this strategy in mind, when considering other problems. Thanks! Jul 26, 2010 at 4:28

I came up with the following alternative way:

``````=LEN(REGEXREPLACE(SUBSTITUTE(SUBSTITUTE(A1;"@";"");"John";"@");"[^@]";""))
``````

Explanation:

• We choose a special character (can be any character not in the target sub-string) and remove it from the string
• We replace the sub-string we are searching for with this char
• We replace any character that is not our special character with nothing
• We count the length of the resulting string and this is the number of occurrences of the sub-string.
• You're right sorry about that as I found this thread seeking a solution for a problem with Google Sheet I didn't even think about excel ... it's fixed. Jun 28, 2018 at 21:38

The code given by @erwaman has to work with delimiters.

``````=COUNTIF(SPLIT(A1,","),"John")
``````

The alternative code given by @variant doesn't need delimiters.

``````=(LEN(A1)-LEN(SUBSTITUTE(A1,"John",)))/LEN("John")
``````

I was working with cells that had a list separated by commas. I needed to count how long the list was and the second bit of code worked like a charm.

If the string you are searching for happens to be 1 character like "," or " " you can simplify it to this.

``````=LEN(A1)-LEN(SUBSTITUTE(A1,",",))
``````

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.