Conversion of column list to comma delimited string

[rn119]

Is there a macro I can use that would convert this...

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]USA[/TD]
[/TR]
[TR]
[TD]Germany[/TD]
[/TR]
[TR]
[TD]Chile[/TD]
[/TR]
[TR]
[TD]France[/TD]
[/TR]
[TR]
[TD]Turkey[/TD]
[/TR]
[TR]
[TD]Indonesia[/TD]
[/TR]
</tbody>[/TABLE]

to this....

USA,Germany,Chile,France,Turkey,Indonesia

The column will have always data in Column A and rows could vary in terms of data input.

 

[My Aswer Is This]

So in this example you have:

A1 USA
A2 Germany

Etc.

So where do you want the results entered?


Would it be B1:

USA,Germany

 

[Michael M]

Try using

Sub MM1()
Dim i As Integer, s As String
i = 1
Do Until Cells(i, 1).Value = ""
    If s = "" Then
        s = Cells(i, 1).Value
    Else
        s = s & "," & Cells(i, 1).Value
    End If
    i = i + 1
Loop
Cells(1, 2).Value = s
End Sub

 

[Peter_SSs]

Assuming the list in column A starting at A2, try

Sub Comma_List()
  Range("B2").Value = Join(Application.Transpose(Range("A2", Range("A" & Rows.Count).End(xlUp))), ",")
End Sub

Although you asked for a macro, this can also be done by formula if your Excel version has the TEXTJOIN function

=TEXTJOIN(",",1,A2:A7)

 

[rn119]

Thanks Michael/Peter. Both of these macros worked great.

 

[Peter_SSs]

Thanks Michael/Peter. Both of these macros worked great.

You're welcome.

 

[Michael M]

@Peter_SSs
I'm ever impressed...ray:

 

[Peter_SSs]

@Peter_SSs
I'm ever impressed...ray:

Thanks Michael.

BTW, if you were to use looping you could compact this coloured part considerably by using only the red line. All that means is that s ends up being a string beginning with a comma which you can easily deal with when writing the result to the sheet by simply omitting that first character.

Cells(1, 2).Value = Mid(s, 2)

Try using

Sub MM1()
Dim i As Integer, s As String
i = 1
Do Until Cells(i, 1).Value = ""
 [COLOR="#0000FF"]   If s = "" Then
        s = Cells(i, 1).Value
    Else
        [COLOR="#FF0000"]s = s & "," & Cells(i, 1).Value[/COLOR]
    End If[/COLOR]
    i = i + 1
Loop
Cells(1, 2).Value = s
End Sub

 

[Michael M]

Aww stop it, now you're just making me feel redundant..
Thanks for the pointer.

 

[rn119]

Michael/Peter...one more question here. Can we concatenate open and close brackets to this macro? So basically, the end result would look like...

(USA,Germany,Chile,France,Turkey,Indonesia)