Excel escaping quotes or apostrophes in cell values

sql excel replace apostrophe substitution

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.

sql excel replace apostrophe substitution

Similar to Roland:

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

sql excel replace apostrophe substitution

Needed to do something similar so I could "escape" double quotes:

=CONCATENATE(CHAR(34),"hello",CHAR(34))

Result:

CodeHunter