CODEWITHSUNDEEP
excel
rcs1057
rcs1057

Reputation: 25

How to combine thousands of columns into a single cell with comma separated values

I have a few thousand columns in an excel doc and I'd like to combine them into a single cell and make them comma separated (without a space in between) so i can put the data in a SQL query.

Here is what it looks like today

A1 | 705565 739815  654970  769828  693798  277205  353553 ...

Here is what I'd like it to look like

A1| 705565,739815,654970,769828,693798,277205,353553...

In the past i've used a simple concatenate formula (A1&","&B1&","&C1) but this data set is far too big. Any suggestions would be great!

Upvotes: 0

Views: 1542

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

If all the values are in row #1, then try this small macro:

Sub KonKate()
    Dim s As String, N As Long, i
    Dim v As String
    N = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 1 To N
        v = Cells(1, i).Text
        If v <> "" Then
            s = s & v & ","
        End If
    Next i
    Range("A1").Value = Mid(s, 1, Len(s) - 1)
End Sub

Upvotes: 1

Related Questions