How do I concatenate text that actually contains quotation marks?

[Guest]

I'm building tiresome SQL statements by concatenating text and cell contents.
I need to be able to insert double quotation marks into the actual text
strings for concatenation. Excel won't allow this because to concatenate the
strings themselves, they must be enclosed in double quotation marks.

Example: =CONCATENATE("extvalue"&C1174&" "&D1174&",") gives me "extvalue211
Legal Name," when what I actually want is "extvalue211 "Legal Name"". (And
no, I don't want to rename all my SQL column headers to contain underscores
and no spaces!)

 

[Bob Phillips]

=CONCATENATE("extvalue"&C1174&" """&D1174&""",")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

In addition to Bob's suggestion, you can also use CHAR(34) to put double
quotes into a string

I understand you don't want to change your column names, but the MVPs on the
SQL Server newsgroup are VERY militant about that subject, indicating that
using spaces in column names is contrary to best practice.

 

[Guest]

Thanks v much for all solutions. And now I have figured out I can put
underscores as well if I really want (per the SQL Server Police
recommendation):
=CONCATENATE("extvalue"&C1171&" "&SUBSTITUTE(D1171," ","_")&",")
I'm good to go!