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)?
Here's an approach that works in Google spreadsheets:
=COUNTIF(SPLIT(A1,","),"John")
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!
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")
Only for Google Sheets
I came up with the following alternative way:
=LEN(REGEXREPLACE(SUBSTITUTE(SUBSTITUTE(A1;"@";"");"John";"@");"[^@]";""))
Explanation:
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.