a quotation mark (") in an excel formula

[Mike]

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]

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]

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]

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]

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]

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]

> 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]

Can you give an example of the formula. Did you try Char?

[Craigb]

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]

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]

how about using tripple "'s as in:

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

[Mike]

> 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]

I used:

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

Is this what you're looking for?

[Capt Jimbo]

Oops should be more like this :-)

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

[Mike]

> Oops should be more like this :-)
>
> =""""&(A1&B1)&""""&(C1)

Ah that works!

Thanks Capt Jimbo, and everyone else for your suggestions.

Mike.