PDA

View Full Version : a quotation mark (") in an excel formula



Mike
22-07-2003, 10:53 AM
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?

Mike.

parry
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 &.

HTH

Mike
22-07-2003, 01:32 PM
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...

Mike.

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

parry
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"

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

Mike
22-07-2003, 08:36 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.

No, I'm trying to use extra quotation marks within the formula.

Mike.

parry
22-07-2003, 08:42 PM
Can you give an example of the formula. Did you try Char?

Craigb
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

Craigb

Mike
23-07-2003, 08:11 AM
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.

Thanks,

Mike.

wuppo
23-07-2003, 08:45 AM
how about using tripple "'s as in:

=concatenate("""text field """,a1,b1," ",c1)

Mike
23-07-2003, 09:36 AM
> 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".

Mike.

Capt Jimbo
23-07-2003, 10:00 AM
I used:

="text field"&(A1&B1)&"text field"&(C1)

Is this what you're looking for?

Capt Jimbo
23-07-2003, 10:15 AM
Oops should be more like this :-)

=""""&(A1&B1)&""""&(C1)

Mike
23-07-2003, 10:25 AM
> Oops should be more like this :-)
>
> =""""&(A1&B1)&""""&(C1)

Ah that works!

Thanks Capt Jimbo, and everyone else for your suggestions.

Mike.