I am trying to set up an IF function, and can't figure out why it isn't
working. When I set up =IF(R2="Diameter",0), I get the correct answer. If I
ask =IF(R2="1/4" - 1 color",0), I get an error. I assume it is because the
look-up has the inch abbreviation (") and it is getting confused with a quote
mark. Because I am dealing with inch sizes, how can I get around this
without completely reconfiguring my whole worksheet?

One way is to replace the " with CHAR(34), i.e.

=IF(R2="1/4"&CHAR(34)&" - 1 color",0)

"Suz" wrote:

I am trying to set up an IF function, and can't figure out why it isn't
working. When I set up =IF(R2="Diameter",0), I get the correct answer. If I
ask =IF(R2="1/4" - 1 color",0), I get an error. I assume it is because the
look-up has the inch abbreviation (") and it is getting confused with a quote
mark. Because I am dealing with inch sizes, how can I get around this
without completely reconfiguring my whole worksheet?

I am trying to set up an IF function, and can't figure out why it
isn't working. When I set up =IF(R2="Diameter",0), I get the correct
answer. If I ask =IF(R2="1/4" - 1 color",0), I get an error. I
assume it is because the look-up has the inch abbreviation (") and it
is getting confused with a quote mark.

One way:
=IF(R2="1/4"&CHAR(34)&" - 1 color",0,"something else")

The ampersand (&) is concatenation of strings.

The double-quote is character number 34.

I added "something else" to make it obvious when there's a mismatch.

daddylonglegs wrote:
One way is to replace the " with CHAR(34), i.e.
=IF(R2="1/4"&CHAR(34)&" - 1 color",0)

That's the best I can come up with, too. But usually there is an
"escape" or "dammit" character in a language that allows us to specify
even the string terminator within a string. For example, in C, "1/4\"
-1 color" would work. Does one exist in Excel? If so, how would I
discover it with Excel Help? That is, what would I search for using
Excel Help (locally, not online)?

Just double up on the special char to use.

=IF(R2="1/4"" - 1 color",0)

HTH,
Elkar



" wrote:

daddylonglegs wrote:
One way is to replace the " with CHAR(34), i.e.
=IF(R2="1/4"&CHAR(34)&" - 1 color",0)

That's the best I can come up with, too. But usually there is an
"escape" or "dammit" character in a language that allows us to specify
even the string terminator within a string. For example, in C, "1/4\"
-1 color" would work. Does one exist in Excel? If so, how would I
discover it with Excel Help? That is, what would I search for using
Excel Help (locally, not online)?

Elkar wrote:
Just double up on the special char to use.
=IF(R2="1/4"" - 1 color",0)

Fascinating! Any idea how I would have learned this on my own using
Excel Help (local, not online)? That is, what would I search for in
Excel Help to learn the syntax of strings. I guess I do not know what
Excel constants of the form "....". I tried "string syntax" and
"string constant", to no avail.

Hmm... There's a good question. I couldn't find anything about it either.
There is an obscure reference to using double ampersand (&&) characters in
headers/footers, but thats about all I could find.

" wrote:

Elkar wrote:
Just double up on the special char to use.
=IF(R2="1/4"" - 1 color",0)

Fascinating! Any idea how I would have learned this on my own using
Excel Help (local, not online)? That is, what would I search for in
Excel Help to learn the syntax of strings. I guess I do not know what
Excel constants of the form "....". I tried "string syntax" and
"string constant", to no avail.