Search code examples
sqlexcelreplaceapostrophesubstitution

Excel escaping quotes or apostrophes in cell values

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?

Solution

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