COUNTIF is not finding strings that include brackets
eg,
A1 LIST OF STRINGS
A2 THIS IS A STRING
A3 THIS IS A STRING WITHOUT BRACKETS
A4 THIS IS A STRING (WITH BRACKETS)
=COUNTIF(column,"THIS IS A STRING") returns 3
=COUNTIF(column,"THIS IS A STRING WITHOUT BRACKETS") returns 1
=COUNTIF(column,"THIS IS A STRING (WITH BRACKETS)") returns 0
How can I count the number of instances of the string with (brackets)?
Thank you!
⇒ Tools ⇒ Options ⇒
OR escape the braces in your Formula like so:
=COUNTIF(column,"THIS IS A STRING \(WITH BRACKETS\)")
With Enable wildcards in formulas also works.
Thanks, that’s great.
I want to use regex in some places, but not here - so escaping the brackets is attractive.
But I simplified my question: actually the formula is not
=COUNTIF("string",column)
It’s
=COUNTIF(cell,column)
I’ve tried this:
=COUNTIF(SUBSTITUTE(cell, "(" , "\(" ),column)
but I get Err:504
Any further thoughts?
[sorry, I got the sytanx wrong in the OP: should be COUNTIF(searchterm,column), not COUNTIF(column,searchterm) as I had it. Your response repeats the error but is perfectly understood, I hope.]
I believe that the help for COUNTIF (just as for any other function allowing regex) already has the necessary further thoughts - just read its part concerning regex. Pay attention to the \Q…\E metacharacters mentioned there.
NO, the syntax is
=COUNTIF( cellrange ; searchexpression )@mikekaganski thanks. As you may infer, I’m not very expert, even in reading documentation.
Can you help me with what this means?
you must either precede every regular expression metacharacter or operator with a " \ " character, or enclose the text into \Q…\E.
Does this mean a search for string with (brackets) can be made to work if I call it \Qstring with (brackets)\E ?
And how does it work when the search expression is in a cell, the contents of which may change?
Even if I have a helper cell that runs a SUBSTITUTE to escape the brackets, and then COUNTIF uses the helper cell as the search expression, nothing is returned.
=COUNTIF(column; "\Q"&A1&"\E")