IF Function

S

Suz

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?
 
D

daddylonglegs

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

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

MyVeryOwnSelf

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.
Click to expand...

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.
 
J

joeu2004

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

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)?
 
E

Elkar

Just double up on the special char to use.

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

HTH,
Elkar
 
J

joeu2004

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

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.
 
E

Elkar

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.
 
Top