In Excel - how to countif() for " (double quotes)

Question

I can use =countif() to find cells with special characters. For example, If I'm looking for cells with '!' I can use =countif("*!*").

How do I do this if I'm looking for cells with '"' (double quotes).

I tried the following but they don't work:

=countif("*"*")
=countif("*~"*")
=countif(~"*"*")

stenci · Answer 1 · 2016-04-27T17:08:33.720

5

You can use double double quotes like this: "abc""def" to create the string abc"def

=COUNTIF(A:A,"*""*")

edited Apr 27 '16 at 17:08

answered Apr 27 '16 at 16:50

stenci

2

It is sad to see this correct answer downvoted. [`Char(34)`](http://stackoverflow.com/a/36896168/11683) is not *the* way, it's *a* way, and arguably, it does you more harm than good if you don't know how to escape quotes. – GSerg Apr 27 '16 at 17:09

Gary's Student · Accepted Answer · 2016-04-27T17:05:52.917

5

Easy:

=COUNTIF(A:A,"*" & CHAR(34) & "*")

and if you wanted to count cells with a pair of double-quotes:

=COUNTIF(A:A,"*" & CHAR(34)  & CHAR(34)& "*")

You can modify this to count "problem" cells, like cells containing CHAR(160),etc.

edited Apr 27 '16 at 17:05

answered Apr 27 '16 at 16:56

Gary's Student

Aw, I think I misread `Double Quotes` to mean `""` not `"`. I believe you are correct. – Scott Craner Apr 27 '16 at 16:57
Great - I did not know about CHAR(34). – val Apr 27 '16 at 17:04
@val thanks for the feedback. – Gary's Student Apr 27 '16 at 17:15

score 0 · Answer 3 · answered Apr 27 '16 at 16:57

0

Try this:

If you are looking for":

=COUNTIF(Range,"*"&CHAR(34)&"*")

If you are looking for "":

=COUNTIF(Range,"*"&CHAR(34)&CHAR(34)&"*")

answered Apr 27 '16 at 16:57

Mrig

3 Answers3