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?
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.
,),
press Ctrl-H (Find & replace)^p,
If you have Office 365 Excel then you can use TEXTJOIN():
=TEXTJOIN(",",TRUE,A:A)
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
Dim entry as variant
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.
An alternative approach would be to paste the Excel column into this in-browser tool:
convert.town/column-to-comma-separated-list
It converts a column of text to a comma separated list.
As the user is copying and pasting to another program anyway, this may be just as easy for them.
You could do something like this. If you aren't talking about a huge spreadsheet this would perform 'ok'...
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. ;)
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 liness indicates substitution/ separates the arguments (i.e., s/find/replace/options)$ represents the end of a line, is the replacement text in this case
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!
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
I improved the generatecsv() sub to handle an excel sheet that contains multiple lists with blank lines separating 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 of course 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 meaningful 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
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.
I did it this way
Removed all the unwanted columns and data, then saved as .csv file, then replaced the extra commas and new line using Visual Studio Code editor. Hola
One of the easiest ways is to use zamazin.co web app for these kind of comma separating tasks. Just fill in the column data and hit the convert button to make a comma separated list. You can even use some other settings to improve the desired output.
http://zamazin.co/comma-separator-tool
Use =CONCATENATE(A1;",";A2;",";A3;",";A4;",";A5) on the cell that you want to display the result.
