Conversion of column list to comma delimited string

rn119

New Member
Joined
Feb 27, 2013
Messages
47
Is there a macro I can use that would convert this...

USA
Germany
Chile
France
Turkey
Indonesia

<tbody>
</tbody>

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

Well-known Member
Joined
Jul 5, 2014
Messages
16,033
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

Well-known Member
Joined
Oct 27, 2005
Messages
17,957
Office Version
2013
Platform
Windows
Try using

Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,693
Office Version
365
Platform
Windows
Assuming the list in column A starting at A2, try
Code:
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)
 
Last edited:

rn119

New Member
Joined
Feb 27, 2013
Messages
47
Thanks Michael/Peter. Both of these macros worked great.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
17,957
Office Version
2013
Platform
Windows
@Peter_SSs
I'm ever impressed...:pray:
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,693
Office Version
365
Platform
Windows
@Peter_SSs
I'm ever impressed...:pray:
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. :)
Rich (BB code):
Cells(1, 2).Value = Mid(s, 2)
Try using

Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
17,957
Office Version
2013
Platform
Windows
Aww stop it, now you're just making me feel redundant..:oops:
Thanks for the pointer.
 

rn119

New Member
Joined
Feb 27, 2013
Messages
47
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)
 
Last edited:

Forum statistics

Threads
1,077,686
Messages
5,335,657
Members
399,032
Latest member
thefinu

Some videos you may like

This Week's Hot Topics

Top