Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have the task of creating a simple Excel sheet that takes an unspecified number of rows in Column A like this:

1234
123461
123151
11321

And make them into a comma-separated list in another cell that the user can easily copy and paste into another program like so:

1234,123461,123151,11321

What is the easiest way to do this?

share|improve this question
7  
Part of the solution might be Edit, Paste Special, Transpose to convert the column into a row. If the other program accepts Tabs rather than commas, then you're all set after copying the new row. –  Arjan Feb 3 '11 at 12:00
    
How do you do the opposite of this? Take a comma-separated list and convert it to a column of content? –  stevvve Oct 22 at 17:10

10 Answers 10

Assuming your data starts in A1 I would put the following in column B:

B1:

=A1

B2:

=B1&","&A2

You can then paste column B2 down the whole column. The last cell in column B should now be a comma separated list of column A.

share|improve this answer
1  
+1 I immediately think of the same solution. Simplest! –  wilson Feb 3 '11 at 5:46
    
saved my life ;) –  Fabio B. Oct 9 '13 at 10:32
2  
This is great for a limited number of rows, but (depending on available memory?) the process will cut short if you're concatenating beyond a couple thousand rows, leaving your output value incomplete. Be careful. –  samthebrand Sep 18 at 21:47
  • Copy the column in Excel
  • Open Word
  • "Paste special" as text only
  • Select the data in Word (the one that you need to convert to text separated with ,), press Ctrl-H (Find & replace)
  • In "Find what" box type ^p
  • In "Replace with" box type ,
  • Select "Replace all"
share|improve this answer
    
This works brilliantly. Thanks! –  Michael Oct 3 '12 at 22:31
    
+1 Same as "official" solution here by Microsoft: support.microsoft.com/kb/819964 –  Neo Dec 30 '13 at 15:51
    
very cute solution... :-) –  Saravanan Feb 11 at 6:35
    
+1, easy and quick. –  Fabio Beoni Jul 28 at 10:44
up vote 8 down vote accepted

I actually just created a module in VBA which does all of the work. It takes my ranged list and creates a comma-delimited string which is output into the cell of my choice:

Function csvRange(myRange As Range)
    Dim csvRangeOutput
    For Each entry In myRange
        If Not IsEmpty(entry.Value) Then
            csvRangeOutput = csvRangeOutput & entry.Value & ","
        End If
    Next
    csvRange = Left(csvRangeOutput, Len(csvRangeOutput) - 1)
End Function

So then in my cell, i just put =csvRange(A:A) and it gives me the comma-delimited list.

share|improve this answer
1  
Ah.. pretty close to my answer, but I like your implementation. –  mpeterson Feb 3 '11 at 14:27
    
Thanks! This worked very well for me. –  muncherelli Feb 4 '11 at 16:18

You could do something like this. If you aren't talking about a huge spreadsheet this would perform 'ok'...

  • Alt-F11, Create a macro to create the list (see code below)
  • Assign it to shortcut or toolbar button
  • User pastes their column of numbers into column A, presses the button, and their list goes into cell B1.

Here is the VBA macro code:

Sub generatecsv() 

Dim i As Integer
Dim 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

Be sure to set the format of cell B1 to 'text' or you'll get a messed up number. I'm sure you can do this in VBA as well but I'm not sure how at the moment, and need to get back to work. ;)

share|improve this answer
    
Excellent answer! –  muncherelli Feb 4 '11 at 16:27
    
Great solution. –  Tim B James Sep 28 '12 at 9:23

Use vi, or vim to simply place a comma at the end of each line:

%s/$/,/

To explain this command:

  • % means do the action (i.e., find and replace) to all lines
  • s indicates substitution
  • / separates the arguments (i.e., s/find/replace/options)
  • $ represents the end of a line
  • , is the replacement text in this case
share|improve this answer
2  
I don't see any Excel user even daring to use Vim... –  Ivo Flipse Feb 6 '11 at 22:58
    
Agreed. Not even close. –  muncherelli Feb 6 '11 at 23:59

You could use How-To Geek's guide on turning a row into a column and simply reverse it. Then export the data as a csv (comma-deliminated format), and you have your plaintext comma-seperated list! You can copy from notepad and put it back into excel if you want. Also, if the you want a space after the comma, you could do a search & replace feature, replacing "," with ", ". Hope that helps!

share|improve this answer
    
Exporting to CSV is beyond what this user can do. I need it to be point and click within excel. –  muncherelli Feb 2 '11 at 16:15

muncherelli, I liked your answer, and I tweaked it :). Just a minor thing, there are times I pull data from a sheet and use it to query a database. I added an optional "textQualify" parameter that helps create a comma seperated list usable in a query.

Function csvRange(myRange As Range, Optional textQualify As String)
    'e.g. csvRange(A:A)  or csvRange(A1:A2,"'") etc in a cell to hold the string
    Dim csvRangeOutput
    For Each entry In myRange
        If Not IsEmpty(entry.Value) Then
            csvRangeOutput = csvRangeOutput & textQualify & entry.Value & textQualify & ","
        End If
    Next
    csvRange = Left(csvRangeOutput, Len(csvRangeOutput) - 1)
End Function
share|improve this answer

I improved the generatecsv() sub to handle an excel sheet that contains multiple lists with blank lines seperating both the titles of each list and the lists from their titles. example

list title 1

item 1
item 2

list title 2

item 1
item 2

and combines them ofcourse into multiple rows, 1 per list.

reason, I had a client send me multiple keywords in list format for their website based on subject matter, needed a way to get these keywords into the webpages easily. So modified the routine and came up with the following, also I changed the variable names to meaninful names:

    Sub generatecsv()

      Dim dataRow As Integer
      Dim listRow As Integer
      Dim data As String

      dataRow = 1: Rem the row that it is being read from column A otherwise known as 1 in vb script
      listRow = 1: Rem the row in column B that is getting written

      Do Until Cells(dataRow, 1).Value = "" And Cells(dataRow + 1, 1).Value = ""
        If (data = "") Then
          data = Cells(dataRow, 1).Value
        Else
          If Cells(dataRow, 1).Value <> "" Then
            data = data & "," & Cells(dataRow, 1).Value
          Else
            Cells(listRow, 2).Value = data
            data = ""
            listRow = listRow + 1
          End If
        End If
        dataRow = dataRow + 1
      Loop

      Cells(listRow, 2).Value = data

    End Sub
share|improve this answer

Sux2Lose's answer is my preferred method, but it doesn't work if you're dealing with more than a couple thousand rows, and may break for even fewer rows if your computer doesn't have much available memory.

Best practice in this case is probably to copy the column, create a new workbook, past special in A1 of the new workbook and Transpose so that the column is now a row. Then save the workbook as a .csv. Your csv is now basically a plain-text comma separated list that you can open in a text editor.

Note: Remember to transpose the column into a row before saving as csv. Otherwise Excel won't know to stick commas between the values.

share|improve this answer

Use =CONCATENATE(A1;",";A2;",";A3;",";A4;",";A5) on the cell that you want to display the result.

share|improve this answer

protected by Pops Oct 6 at 22:58

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.