Data in Column A to Comma Delimited List

**webdivx** · 05-31-2012, 11:52 AM

I have an excel spreadsheet that has data in column A like in the attached example spreadsheet. How can I make this into a comma delimited list? So something like this...

food
drinks
home
shelter
ocean
water

to this ---> food, drinks, home, shelter, ocean, water

**rvasquez** · 05-31-2012, 12:06 PM

How long is your data?

If this is the entire list then this will work

=CONCATENATE(A1,", ",A2,", ",A3,", ",A4,", ",A5,", ",A6)

**jraj1106** · 05-31-2012, 12:08 PM

Hi

Try this code. Not sure about formula:

Sub COMBINEDATA()
    Dim rcnt As Long, mergestr As String
    
    rcnt = Range("A" & Rows.Count).End(xlUp).Row
    mergestr = ""
    
    For i = 1 To rcnt
        If mergestr = "" Then
            mergestr = Range("A" & i).Value
        Else
            mergestr = mergestr & "," & Range("A" & i).Value
        End If
        Range("B1").Value = mergestr
    Next
End Sub

**webdivx** · 05-31-2012, 01:23 PM

Thank you jraj1106, this works perfectly and was exactly what I was looking for!!!

My spreadsheet has over 10K rows so manually doing that function would be troublesome rvasquez, but I also thank you for taking the time to share your knowledge!

**goldberg96** · 07-11-2013, 02:11 PM

Hi folks. New to forum. I know, really old thread but I need to do exactly this. However, I don't understand where that macro gets written. Anyone able to give me some more clarification as to how to make this work?

Thanks .......... Rob

**arlu1201** · 07-11-2013, 02:16 PM

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button