18

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!

TheRealPapa
  • 4,393
  • 8
  • 71
  • 155

6 Answers6

29

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))
Alec.
  • 5,371
  • 5
  • 34
  • 69
15

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)
Jerry
  • 70,495
  • 13
  • 100
  • 144
  • You use a bunch of " until Excel understands??? This is not magic: doubling quotes is way Excel quotes them. Also, you can drop the 1 at the end if you want to check the whole string??? Excel may correct it well or not!!! -1 for laziness – LS_ᴅᴇᴠ Sep 17 '13 at 10:56
  • 2
    @LS_dev The bunch of `"` was a joke, okay? Anyone could get confused by the sheer number of `"` in a formula. Also, of course you can drop the 1 at the end, because `1` means start from character 1, which is basically what `FIND(find_text, within_text)` does. Excel does not correct it, Excel takes 1 as the default as this is how it works. Btw, check your own formula, it returns `#VALUE`. – Jerry Sep 17 '13 at 11:00
  • Hum... Ok, misunderstood "1 at end" point (understood "removing one quote at end"). But you still didn't give a clear explanation. Can't remove downvote now. – LS_ᴅᴇᴠ Sep 17 '13 at 11:04
3

You could use search too

=SEARCH("""";A1)

enter image description here

MrSimpleMind
  • 7,890
  • 3
  • 40
  • 45
  • why does this work? shouldn't it find an instance of two double quotes next to each other. This behaviour is very confusing on Excel's part. – Gabe O'Leary Apr 09 '19 at 22:40
1

If you are just using the search and replace function use ~ to search for " / " use ~" / ~" ~ work for any odd character

Worker
  • 11
  • 1
1

I had a similar problem.

I wanted Excel to select the part of a sentence that was -- I thought -- between quotation marks " " (or CHAR(34) in excel) while in fact it was between a

Left double quotation mark (or CHAR(147) in excel) “

and a

Right double quotation mark (or CHAR(148) in excel) ”

In cell A1 I had a text string that went pretty much as follows:

blablablabla “None of the sectors reported in 2000 blablabla”

I wanted Excel to select the only part I was interested in which was :

--> None of the sectors reported in 2000 <--

You can use this formula: =TEXTBEFORE(TEXTAFTER(A1,"“"),CHAR(148),)

For the sake of a more comprehensive example here I used "“" in the formula TEXTAFTER(A1,"“") but I could have used TEXTAFTER(A1,CHAR(147)

Similarly with the TEXTBEFORE formula where I used CHAR(148), but I could have used "”" instead.

Anyway just make sure you don't get confused with “ ” (right and left double quotation marks) and " " (quotation mark).

Thomas
  • 1,008
  • 3
  • 16
  • 34
DK's little contribution
  • 11
  • 4
0

Correct way of escaping quotes inside strings, in Excel formulas is doubling them:

=FIND(A1, """")

will return first quote found in A1 (or error if not found).

LS_ᴅᴇᴠ
  • 10,823
  • 1
  • 23
  • 46