8

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)?

6 Answers 6

8

Here's an approach that works in Google spreadsheets:

=COUNTIF(SPLIT(A1,","),"John")
4
  • 1
    Is this specific to certain versions of Excel? I was not aware that SPLIT works outside of VBA.
    – fixer1234
    Feb 6, 2015 at 7:28
  • @fixer1234: I think this only works in Google spreadsheets. I've clarified the answer.
    – erwaman
    Feb 9, 2015 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. Mar 30, 2015 at 14:43
  • Works for me: docs.google.com/spreadsheets/d/…
    – erwaman
    Mar 31, 2015 at 5:31
8

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!

3
  • How to do this for multiple cells? so if you would have multiple cells like A1?
    – Michahell
    Feb 3, 2015 at 4:41
  • The SPLIT function works outside of VBA?
    – fixer1234
    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 ;.
    – Kenneth L
    Jun 29, 2018 at 2:14
2

I think you probably found the best way.

An alternative:

=(LEN(A1)-LEN(SUBSTITUTE(A1,"John",)))/LEN("John")
2
  • That's an interesting approach, using LEN. Will keep this strategy in mind, when considering other problems. Thanks!
    – johnL
    Jul 26, 2010 at 4:28
  • This is the answer I had to use for Google Spreadsheets. Mar 30, 2015 at 14:43
2

Only for Google Sheets

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.
1
  • 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.
    – Gneuh
    Jun 28, 2018 at 21:38
1

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,",",))
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .