Combine rows of data into one cell, separated by quotes and comma

-3

I have the following data:

2
3
4
5
6
7
8
9
10
apple
green
hi

I would like to consolidate all of this into one value, like so:

'2','4','5','6','7','8','9','10','apple','green','hi'

So I need to take data from rows, paste them into an Excel file, run this macro and get them all in one cell (with commas and quotes). I will then put them in a WHERE IN clause in an SQL statement for faster searches. If there is an easier way or a tool, please let me know.

Here is the intended use in SQL:

Select *
from Blah..Table
where IDs in 
(
'2','4','5','6','7','8','9','10','apple','green','hi'
)

2012-04-04 16:57
by user1294121

OP: the question, as written at 2012-04-04 16:57:37Z, shows: 1. no research effort, 2. no real code from an attempt to solve the problem, 3. no error message from any such attempt. no personal offense intended; i have downvoted this question. if you fix one or more of the above issues -- by editing the question -- i will remove my downvote as soon as i can. please read the faq about how to ask a good question. thank you, and best of luck with your projec - bernie 2012-04-04 17:03

You could run this search in the box at the top right of this page: [excel] concatenate for example - assylias 2012-04-04 17:06

I wrote a user defined function called JoinRange that does that. You can get the code here

http://www.dailydoseofexcel.com/archives/2012/03/31/joinrange-update/

You would use it like

=joinrange(A1:A12,,"','","'","'")

which gives the result

'2','3','4','5','6','7','8','9','10','apple','green','hi'

You can also call it from VBA.

2012-04-04 19:33
by Dick Kusleika