Convert a column into a comma separated list

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? 12.8k 11 11 gold badges 53 53 silver badges 78 78 bronze badges asked Feb 2, 2011 at 16:01 muncherelli muncherelli 2,529 4 4 gold badges 26 26 silver badges 31 31 bronze badges

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.

Commented Feb 3, 2011 at 12:00

How do you do the opposite of this? Take a comma-separated list and convert it to a column of content?

Commented Oct 22, 2014 at 17:10 @stevvve use Text to Columns feature in the data tab. support.microsoft.com/en-us/kb/214261 Commented Sep 24, 2015 at 17:42

I use any regular expression capable text editor for this, like notepad++. Copy column values and paste it in the editor, search and replace regular expression, find "\r\n" replace with ",". If you want to convert CSV to column, find "," and replace with "\r\n"

Commented Jul 20, 2017 at 20:27

14 Answers 14

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

=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.

answered Feb 2, 2011 at 16:37 3,357 2 2 gold badges 16 16 silver badges 17 17 bronze badges

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.

Commented Sep 18, 2014 at 21:47

Using Excel 2013 on a powerful Windows 10 Pro computer, this failed after only 30-35 lines. I needed well over 200. Solution by Michael Joseph worked perfectly.

Commented Jun 5, 2019 at 19:30

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"

1,504 1 1 gold badge 17 17 silver badges 22 22 bronze badges answered Jun 5, 2012 at 5:24 Michael Joseph Michael Joseph 1,081 1 1 gold badge 7 7 silver badges 2 2 bronze badges +1 Same as "official" solution here by Microsoft: support.microsoft.com/kb/819964 Commented Dec 30, 2013 at 15:51 very cute solution. :-) Commented Feb 11, 2014 at 6:35 As a last step -> Remove the , after the last number. :-) Commented May 30, 2021 at 15:33

Even after more than a decade, this is still the only reasonable working solution in converting multiple values to a single text value when working with a local Excel file.

Commented May 16, 2023 at 14:24

If you have Office 365 Excel then you can use TEXTJOIN():

=TEXTJOIN(",",TRUE,A:A)

enter image description here

answered Jan 25, 2017 at 18:05 Scott Craner Scott Craner 23.5k 3 3 gold badges 23 23 silver badges 26 26 bronze badges As Scott points out, important caveat is Excel 365 only. Function does not exist in Excel 2016. Commented Sep 30, 2020 at 20:07 If you need a quote separated list 'a','b','c' , try ="'" & TEXTJOIN("','",TRUE,A:A) & "'" Commented Oct 28, 2020 at 19:31 The most elegant solution! Commented Mar 2, 2022 at 11:22 ✅ Accepted answer. If you have an input array: =REDUCE(,F7:F10, LAMBDA(a,b,CONCAT(a,", ",b))) Commented Apr 6, 2023 at 10:46

Nice answer, unless you have a big list. I think it's limited by the max characters a cell can contain: 32,767 characters

Commented May 9, 2023 at 18:50

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.

14.8k 40 40 gold badges 108 108 silver badges 171 171 bronze badges answered Feb 3, 2011 at 14:10 muncherelli muncherelli 2,529 4 4 gold badges 26 26 silver badges 31 31 bronze badges Ah.. pretty close to my answer, but I like your implementation. Commented Feb 3, 2011 at 14:27

I like it. You may just want to check to see if Len(csvRangeOutput) is 0 before returning csvRange. If 0, you'll have a run time error.

Commented Sep 30, 2020 at 20:42

An alternative approach would be to paste the Excel column into this in-browser tool:

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.

answered Apr 22, 2015 at 20:46 319 2 2 silver badges 3 3 bronze badges

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

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

741 1 1 gold badge 10 10 silver badges 19 19 bronze badges answered Feb 6, 2011 at 22:53 Breathwork Breathwork 79 1 1 bronze badge

Disagree with comments because of context. Someone with a superuser account who posts here is often the type to use vim.

Commented Mar 1, 2016 at 20:25

I but use ViM and think the basic idea is useful. I ended up solving the problem by using the idea and a simple text editor.

Commented Dec 16, 2016 at 17:16

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. ;)

14.8k 40 40 gold badges 108 108 silver badges 171 171 bronze badges answered Feb 2, 2011 at 19:12 551 2 2 silver badges 4 4 bronze badges

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!

answered Feb 2, 2011 at 16:12 717 5 5 silver badges 18 18 bronze badges Exporting to CSV is beyond what this user can do. I need it to be point and click within excel. Commented Feb 2, 2011 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

14.8k 40 40 gold badges 108 108 silver badges 171 171 bronze badges answered Apr 8, 2011 at 15:54 21 1 1 bronze badge

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.

answered Oct 6, 2014 at 22:56 samthebrand samthebrand 340 1 1 gold badge 5 5 silver badges 22 22 bronze badges

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