is there anyway in excel to take a column and convert it to a concatenated string

Question

i want to take a list of name that are in multiple rows a single excel column, like this:

Joe
Bob
George

and convert that into one cell that has this:

"Joe", "Bob", "George"

Answer 1

If the Excel CONCATENATE() function won't do what you want, this link might help.

Answer 2

It is this easy. You might want to use in combination with trim, left, right, and find. Enjoy!

= A1 & " " & B1

Answer 3

You can use GetString with an ADO recordset.

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String

''This is not the best way to refer to the workbook
''you want, but it is very conveient for notes
''It is probably best to use the name of the workbook.

strFile = ActiveWorkbook.FullName

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT * " _
       & "FROM [Sheet1$A:A] "

rs.Open strSQL, cn, 3, 3

''http://www.w3schools.com/ado/ado_getstring.asp
''str = rs.GetString(format,rows,coldel,rowdel,nullexpr)
s = rs.GetString(, , , ", ")
ActiveSheet.Range("B1") = s

''Tidy up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing