Conversion of column list to comma delimited string

rn119

New Member
Joined
Feb 27, 2013
Messages
49
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Actually i have one more thing. In that same list, if USA were to occur twice, is there a way for either macro to eliminate duplicates?
 
Upvote 0
In that same list, if USA were to occur twice, is there a way for either macro to eliminate duplicates?
Assuming data starts in cell A1 and output is to go to cell B1, then give this a try...
Code:
Sub UniqueList()
  Dim X As Long, Data As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Value
  With CreateObject("Scripting.Dictionary")
    For X = 1 To UBound(Data)
      .Item(Data(X, 1)) = 1
    Next
    Range("B1").Value = Join(.Keys, ",")
  End With
End Sub
 
Upvote 0
Or

a) If you have the TEXTJOIN function in your version of Excel
Code:
Sub Comma_List_v2()
  Dim addr As String
  
  addr = Range("A1", Range("A" & Rows.Count).End(xlUp)).Address
  Range("B1").Value = Evaluate(Replace("=TEXTJOIN("","",1,IF(MATCH(#,#,0)=ROW(#)-" & Range(addr).Row & " +1,#,""""))", "#", addr))
End Sub

b) Otherwise
Code:
Sub Comma_List_v3()
  Dim c As Range
  Dim s As String
  
  For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
    If InStr(s, "," & c.Value & ",") = 0 Then s = s & "," & c.Value & ","
  Next c
  Range("B1").Value = Replace(Mid(s, 2, Len(s) - 2), ",,", ",")
End Sub
 
Upvote 0
b) Otherwise
Rich (BB code):
Sub Comma_List_v3()
  Dim c As Range
  Dim s As String
  
  For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
    If InStr(s, "," & c.Value & ",") = 0 Then s = s & "," & c.Value & ","
  Next c
  Range("B1").Value = Replace(Mid(s, 2, Len(s) - 2), ",,", ",")
End Sub

I am pretty sure you know this already, but you can omit the third argument to the Mid function that I highlighted in red above. Unlike Excel's MID function, the third argument to VB's Mid function is optional and, when omitted, returns the remainder of the text automatically.
 
Last edited:
Upvote 0
I am pretty sure you know this already, but you can omit the third argument to the Mid function that I highlighted in red above. Unlike Excel's MID function, the third argument to VB's Mid function is optional and, when omitted, returns the remainder of the text automatically.
Yes I do know that Rick but if I omit that argument here I would not eliminate the comma that is at the end of my string at that point. :)
 
Upvote 0
Yes I do know that Rick but if I omit that argument here I would not eliminate the comma that is at the end of my string at that point. :)
Ah, I see. I did not look carefully at all of your code and, given a quick look, thought you did the code line inside the loop like I do in this kind of code...

If InStr("," & s & ",", "," & c.Value & ",") = 0 Then s = s & "," & c.Value

which then would have only had a leading comma to remove.
 
Upvote 0
Yeah, just a slightly different approach. Mine has a little extra manipulation to do once at the end, yours has a tiny bit more concatenation to do each time through the loop. No real difference either way as I see it. :)
 
Upvote 0
Peter,

i tried your approach and it works somewhat. I'll give you more of a defined output here to work with.



USA100
Germany200
Chile300
France100
Turkey400
Indonesia500
USA300

<tbody>
</tbody>




I need the data to come out like this...


(USA,Germany,Chile,France,Turkey,Indonesia) in row C1
(100,200,300,400,500) in row C2




Currently the data is coming out like this...




(USA,Germany,Chile,France,Turkey,Indonesia) in row C1 (correct)
(USA,Germany,Chile,France,Turkey,Indonesia,100,200,300,400,500) in row C2 (incorrect)

I altered your base code to this.

Dim c As Range
Dim s As String

For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
If InStr(s, "," & c.Value & ",") = 0 Then s = s & "," & c.Value & ","
Range("C1").Value = "(" & Replace(Mid(s, 2, Len(s) - 2), ",,", ",") & ")"
Next c
For Each c In Range("B1", Range("B" & Rows.Count).End(xlUp))
If InStr(s, "," & c.Value & ",") = 0 Then s = s & "," & c.Value & ","
Next c
Range("C2").Value = "(" & Replace(Mid(s, 2, Len(s) - 2), ",,", ",") & ")"




Can you let me know what revisions I need to make this work?
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top