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,272
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
18,150
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
42,205
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
18,150
Office Version
2013
Platform
Windows
@Peter_SSs
I'm ever impressed...:pray:
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,205
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
18,150
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,081,615
Messages
5,360,037
Members
400,565
Latest member
Tommy O

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top