Excel escaping quotes or apostrophes in cell values

sql excel replace apostrophe substitution
Mir picture Mir · Sep 26, 2014 · Viewed 46.7k times · Source

I get a lot of database information from clients in excel spreadsheets. I frequently need to insert/update this data back into the database.

I often use excel to generate the insert and update statements via concatenating a bunch of cells together. Sometimes the data includes text cells which can have single quotes in them. If not dealt with carefully, these single quotes will make the SQL statements break.

How can I escape single quotes in text data, via formulas, when concatenating a bunch of cell values together, so that my resulting SQL scripts are valid?

Answer

Mir picture Mir · Sep 26, 2014

The best solution I have found is to use the following:

=SUBSTITUTE(A1, "'", "''")

This will replace all single quotes with two single quotes which, in T-SQL statements escapes the character and treats it as string data.

Related questions

Generate sql insert script from excel worksheet

I have a large excel worksheet that I want to add to my database. Can I generate an SQL insert script from this excel worksheet?

Read More
How to run a SQL query on an Excel table?

I'm trying to create a sub-table from another table of all the last name fields sorted A-Z which have a phone number field that isn't null. I could do this pretty easy with SQL, but I have no clue how …

Read More
Export SQL query data to Excel

I have a query that returns a very large data set. I cannot copy and paste it into Excel which I usually do. I have been doing some research on how to export directly to an Excel sheet. I am …

Read More