[Solved] How do I enclose a double quotation mark in formula

=LEN(F1)-LEN(SUBSTITUTE(F1,CHAR(39),""))

=LEN(F1)-LEN(SUBSTITUTE(F1,CHAR(34),""))

Thank you aladin, but when I tried to substitute the chr(34) in the last part of the formula I got this...

=(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(34),"")))/LENChar(34)

and the formula is generating the #Name? in the cell. Where did I mess up? Thanks again.

OK, I got it to work. I did not enclose the last Chr34 in parens.

=(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(34),"")))/LEN(CHAR(34))

OK, I got it to work. I did not enclose the last Chr34 in parens.

=(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(34),"")))/LEN(CHAR(34))

Click to expand...

No need for the /LEN(...) bit. That's why I omitted that in both formulas.

OK, now I need to find the number of \" occurences there are in a cell. How do I add the \ in front of the above formula? Thank you again.

OK, here we go. This formula counts the number of times the following character string or text string occurs in a cell. The character string or text string found in this formula equals \"

=(LEN(C1)-LEN(SUBSTITUTE(C1,(("\")&(CHAR(34))),"")))/LEN(("\")&(CHAR(34)))

OK, here we go. This formula counts the number of times the following character string or text string occurs in a cell. The character string or text string found in this formula equals \"

=(LEN(C1)-LEN(SUBSTITUTE(C1,(("\")&(CHAR(34))),"")))/LEN(("\")&(CHAR(34)))

Click to expand...

\ is CHAR(92).

Hi,

if have a question:

If you wnat to count the occurances of "" in the cell F1, why is it not enough to take this formula?

<table><tr style="vertical-align:top; text-align:center; "><tr><td>Tabelle3</td></tr><tr><td><table border=1 cellspacing=0 cellpadding=0 style="font-family:Arial,Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "> <style type = "text/css"> th {font-weight:normal} </style> <colgroup><col width=30 style="font-weight:bold; "><col width=79.999998 ><col width=132.999996675 ></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>E</td><td>F</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:center; ">5</td><td style="">ewrwe er   we vv</td></tr></table><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#FFFCF9; "><tr><td>Formeln der Tabelle</td></tr><tr><td><table style="font-family:Arial; font-size:10pt;">E1 : =LEN(F1)-LEN(SUBSTITUTE<span style=' color:008000; '>(F1," ","")</span>)
</table></td></tr></table></td></tr><tr><td> </td></tr></tr></table>

Hi,

if have a question:

If you wnat to count the occurances of "" in the cell F1, why is it not enough to take this formula?

...

=LEN(F1)-LEN(SUBSTITUTE<span style=' color:008000; '>(F1," ",""))

...

Click to expand...

Are you posing a question or making an observation?