Escaping literals in formulas

Question

Level 1

0 points

Escaping literals in formulas

How can I escape a formula value to output text values that include quotes. I want an if formula to ouput the following text exactly as is -- \"http://myref.com\" -- In order to get the equation to take in Numbers, I have to input it as "\"'http://myref.com\""" and then it comes out with both quotation marks which won't work for me. So how do I get it to take the formula and output the text value \"http://myref.com\"

The formula would look like if(A1=0, "Use this", "\"http://myref.com\""). Also tried it with single quotes as in if(A1=0, 'Use this', '\"http://myref.com\"')

Mac Pro, Mac OS X (10.7.2)

Posted on Jan 2, 2012 4:39 PM

Reply

Answer 1

Jan 2, 2012 4:47 PM in response to bostboy

The best way I have found is to use the concatenate operator to prepend and postpend the double quote.

I placed the "surround" character in cell D3, then entered a string in B2, and finally, the result of using the contatenate operator:

B3=D2&B2&D2

I hope this helps

Reply

Answer 2

Jan 2, 2012 4:55 PM in response to bostboy

BB,

Format two column as Text and enter your literal strings there, true case and false case, then reference the strings from elsewhere.

For example, if your text strings are in B, you can use the equation: =IF(A=0, "Use This", B)

Your string -- \"http://myref.com\" -- will retain all it's punctuation if inserted in a column formatted as Text. (I can't figure out how to prevent that URL-like string from acting like a link in the forum software)

Regards,

Jerry

Reply

Answer 3

bostboy Author

Level 1

0 points

Jan 2, 2012 7:07 PM in response to bostboy

I ended up putting the \" in a text cell and referencing that every place it shows up like Wayne suggested and it works great. Thanks so much for the input.

Reply

Answer 4

KOENIG Yvan

Level 8

41,819 points

Jan 3, 2012 7:03 AM in response to bostboy

Here are my two cents :

=CHAR(34)&"http://myref.com"&CHAR(34)

Yvan KOENIG (VALLAURIS, France) mardi 3 janvier 2012

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

My iDisk is : http://public.me.com/koenigyvan

Please : Search for questions similar to your own before submitting them to the community

Reply