View Full Version : a quotation mark (") in an excel formula
How do I tell excel to treat a " in an excel formula as a "? :) Excel reads quotation marks as signifying text inside, but my text field requires a " to be included in it as well, so I need to somehow tell Excel that the " is part of the text field within the two "s :p
Does that make sense?
22-07-2003, 01:02 PM
Hi Mike, this is an interesting one. I've tried a few things including putting two lots of quotes but it didnt work. However, if quoted text is in the cell (rather than a formula directly) then you can get it to work.
Try putting "Mike" in cell a1 then a formula =a1 and it will come up with "Mike" rather than Mike. If you need to join several text strings together use &.
Thanks for the suggestion Parry, but unfortunately in my spreadsheet having the quotation marks in other cells isn't practical :( otherwise I would just do as you suggested, or even just a cell with a single " in it that I could call from the formula...
22-07-2003, 01:49 PM
I'll think a bit more about this then. Meantime, could you post an example of your formula so I can get a better handle on what your trying to achieve.
22-07-2003, 01:55 PM
Ok, thought of something but may not be the answer your looking for. If you use the ASCII number 34 instead of the actual quotes it works.
eg: =CHAR(34)&"Test"&CHAR(34) returns "Test"
22-07-2003, 08:29 PM
Not too sure what you're trying to achieve - but try a single apostrophe at the start of each cell input, followed by your data. That way Excel won't try to extend the data as a formula. Hope this helps.
> Not too sure what you're trying to achieve - but try
> a single apostrophe at the start of each cell input,
> followed by your data. That way Excel won't try to
> extend the data as a formula. Hope this helps.
No, I'm trying to use extra quotation marks within the formula.
22-07-2003, 08:42 PM
Can you give an example of the formula. Did you try Char?
22-07-2003, 11:40 PM
If you don't want to put a cell with the " in your spreadsheet, why not open another sheet and put it there. You can then hide it (the " spreadsheet) so it won't show up when you open the file.
Hows that for this time of the night
here's a much simplified version of my formula:
=concatenate("text field ",a1,b1," ",c1)
to give the results:
text-field "contents-of-a1"contents-of-b1 contents-of-c1
What I need is the contents of column A to be enclosed in quotation marks when taken by the formula, but I CANNOT change the contents of column A to include the quotation marks - I can only have the quotation marks in the formula. However I can't put the quotation marks in to the formula.
Char() seems like it will work, I'm just hoping for something much more convenient.
23-07-2003, 08:45 AM
how about using tripple "'s as in:
=concatenate("""text field """,a1,b1," ",c1)
> how about using tripple "'s as in:
> =concatenate("""text field """,a1,b1," ",c1)
Although that works around the text field, it doesn't when trying to apply it to a cell reference such as A1 (eg """a1""") - this treats A1 as a text field, so the result is "a1".
23-07-2003, 10:00 AM
="text field"&(A1&B1)&"text field"&(C1)
Is this what you're looking for?
23-07-2003, 10:15 AM
Oops should be more like this :-)
> Oops should be more like this :-)
Ah that works!
Thanks Capt Jimbo, and everyone else for your suggestions.
Powered by vBulletin® Version 4.2.1 Copyright © 2014 vBulletin Solutions, Inc. All rights reserved.