CODEWITHSUNDEEP
excel
ero
ero

Reputation: 155

Concatenate function in Excel 2010 to add single quotes and commas

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.

Upvotes: 14

Views: 184603

Answers (6)

Gaurav Singh
Gaurav Singh

Reputation: 97

Simply put single quote inside double quotes when you need to concat it with any other string. for example: concat("'", "A", "'") will give you 'A'.

In other words you treat it like any other character that you use in concat.

Upvotes: -2

Ankit Jindal
Ankit Jindal

Reputation: 4030

To enclose all specified cells in quotes, the following simple formulas may help you.

Method 1:

Use below formula into the blank cell:

=CHAR(34) & A1 & CHAR(34)

Considering the value that you want to enclose in quotes is present in A1 cell

Method 2:

To insert single quotes around the cell values, use this formula:

 ="'" & A1 & "'"

Upvotes: 0

virtualdvid
virtualdvid

Reputation: 2411

Sadly textjoin() function is not in Excel 2010 but if someone is wondering how to concatenate several columns, wrapping the values in a single quote in the latest Excel versions just run this:

=TEXTJOIN(delimeter, ignore empty cells (true|false), usual range of cells)

I used something like this to create a sql script to insert values in a table:

="INSERT INTO my_table values ('" & TEXTJOIN("','", False, A2:Z2) & "')"

check the official docs for further details.

Upvotes: 0

rahul pareek
rahul pareek

Reputation: 139

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

Upvotes: 6

hari
hari

Reputation: 71

Follow the below steps.

1- select all the rows.

2- right click and select format cells

3- select number tab

4- select custom in the left category tab and

5- under the type enter '@' (if column has hello after applying format it will turn into 'hello')

Upvotes: 6

Andrew L
Andrew L

Reputation: 7038

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

="'"&A1&"',"

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

Upvotes: 38

Related Questions