Count number of occurrence of string in google sheet-CodePudding

I have a column, which has data like this :-

C29,C22,C345
C292,C345,C456
C456,C29,C453

Now I want to find number of occurrence of C29, and I am using this formula now:-

COUNTIF(I9:I11,"*C29*")

But it is returning 3, as it also take C292 in an account, how can I set a formula to match only C29?

Thanks

CodePudding user response：

You can try:

Formula:

=SUMPRODUCT(ISNUMBER(FIND(",C29,",","&A1:A3&",")))

Or, less verbose:

=SUMPRODUCT(REGEXMATCH(A1:A3,"\bC29\b"))

Or, even less verbose:

=SUMPRODUCT(SPLIT(A1:A3,",")="C29")

CodePudding user response：

So, if you have the room, the do Text-to-Columns and get each data element into its own cell. Then extract the numbers, either replace the C with nothing or mid() as shown. Then count the 29.