How to search for " in string - EXCEL

Question

This is a tricky one I am stuck on. In Excel 2010 I want to search a string for the character ". I am using the formula

=FIND(A1,"text", 1) 

which will return a number (starting position) of "text" in A1 string, or an error if not found.

How to search for " in the formula?

Thanks for the advice!

Answer 1

Try amending your formula to search for Char(34), think this will help with readability instead of having 10,000 quotes in your formula.

=IF(COUNT(FIND(CHAR(34),A1))

Answer 2

You use a bunch of " until Excel understands it has to look for one :)

=FIND("""", A1)

Explanation:
Between the outermost quotes, you have "". The first quote is used to escape the second quote so that "" in between quotes means a single double quote.

Also, you can drop the 1 at the end if you want to check the whole string.

Note that it's find character, into cell. Or use CHAR(34) which is the equivalent of a quote:

=FIND(CHAR(34), A1)

Answer 3

You could use search too

=SEARCH("""";A1)