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)?
Asked
Active
Viewed 2.4k times
7
6 Answers
7
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? – Michahell Feb 03 '15 at 04:41
-
The SPLIT function works outside of VBA? – fixer1234 Feb 06 '15 at 07: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 '18 at 02:14
7
Here's an approach that works in Google spreadsheets:
=COUNTIF(SPLIT(A1,","),"John")
-
1Is this specific to certain versions of Excel? I was not aware that SPLIT works outside of VBA. – fixer1234 Feb 06 '15 at 07:28
-
@fixer1234: I think this only works in Google spreadsheets. I've clarified the answer. – erwaman Feb 09 '15 at 02: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 Mar 30 '15 at 14:43
-
Works for me: https://docs.google.com/spreadsheets/d/1xSankYnnJpPNS58orPiD6SoYaGLiEC2eHViRB-Bfahc/edit?usp=sharing – erwaman Mar 31 '15 at 05:31
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.
-
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 '18 at 21:38
2
I think you probably found the best way.
An alternative:
=(LEN(A1)-LEN(SUBSTITUTE(A1,"John",)))/LEN("John")
variant
- 1,890
- 13
- 12
-
That's an interesting approach, using LEN. Will keep this strategy in mind, when considering other problems. Thanks! – johnL Jul 26 '10 at 04:28
-
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,",",))
Dripping D
- 11
- 1
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.
Felix
- 1
- 1