Concatenate function in Excel 2010 to add single quotes and commas

Question

I am working on a loaner laptop and have found that the concatenate function I have used to add single quotes and a comma to a column of text is no longer working. I need this to bring a series of IDs into a SQL query. The function I have used for years is =concatenate("'",A1,"',") to get a result of 'A1',. This is in Microsoft Excel 2010.

Answer 1

Use Excel Characters. In your case it would be :

= concatenate(CHAR(39),A1,CHAR(39))

To get the result = 'A1'

similarly, you can include other special charterers as well. Like to include space you need to use CHAR(32).

Answer 2

Try the following as an alternative to =concatenate():

="'"&A1&"',"

I've been using the above to do exactly what you're trying to accomplish.