Convert a column into a comma separated list

Question

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

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?

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 03 '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 '14 at 17:10
@stevvve use Text to Columns feature in the data tab. https://support.microsoft.com/en-us/kb/214261 — Trevor, Sep 24 '15 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" — junaling, Jul 20 '17 at 20:27

score 168 · Answer 1 · answered Feb 02 '11 at 16:37

168

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.

answered Feb 02 '11 at 16:37

Sux2Lose

3,307
2
15
17

10

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 '14 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. – Trialsman Jun 05 '19 at 19:30

score 104 · Answer 2 · edited Sep 22 '14 at 22:07

104

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"

edited Sep 22 '14 at 22:07

Lee Taylor

1,466
1
17
22

answered Jun 05 '12 at 05:24

Michael Joseph

1,041
1
7
2

+1 Same as "official" solution here by Microsoft: http://support.microsoft.com/kb/819964 – Neo Dec 30 '13 at 15:51
1

very cute solution... :-) – Saravanan Feb 11 '14 at 06:35
1

As a last step -> Remove the `,` after the last number. :-) – Gautham M May 30 '21 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. – Codingwiz May 16 '23 at 14:24

score 86 · Answer 3 · answered Jan 25 '17 at 18:05

86

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

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

answered Jan 25 '17 at 18:05

Scott Craner

22,693
3
21
25

3

As Scott points out, important caveat is Excel 365 only. Function does not exist in Excel 2016. – WhyGeeEx Sep 30 '20 at 20:07
2

If you need a quote separated list `'a','b','c'`, try `="'" & TEXTJOIN("','",TRUE,A:A) & "'"` – Stevoisiak Oct 28 '20 at 19:31
The most elegant solution! – Paul-Sebastian Manole Mar 02 '22 at 11:22
✅ Accepted answer. If you have an input array: =REDUCE(,F7:F10, LAMBDA(a,b,CONCAT(a,", ",b))) – Renato Heeb Apr 06 '23 at 10:46
1

Nice answer, unless you have a big list. I think it's limited by the max characters a cell can contain: 32,767 characters – cs_pupil May 09 '23 at 18:50
@cs_pupil The character limit of the cell is `32,767` so yes, if you have more than that, then Excel is not the tool to use. – Scott Craner May 09 '23 at 18:52
Wow, TEXTJOIN worked in LibreOffice 7.3. – Ejaz May 26 '23 at 08:42

score 26 · Accepted Answer · edited Oct 28 '20 at 19:35

26

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.

edited Oct 28 '20 at 19:35

Stevoisiak

13,555
39
101
154

answered Feb 03 '11 at 14:10

muncherelli

2,409
3
25
28

2

Ah.. pretty close to my answer, but I like your implementation. – mpeterson Feb 03 '11 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. – WhyGeeEx Sep 30 '20 at 20:42

sunset · Answer 5 · 2015-04-22T20:56:08.440

12

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.

edited Apr 22 '15 at 20:56

answered Apr 22 '15 at 20:46

sunset

319
2
3

score 7 · Answer 6 · edited Oct 28 '20 at 19:37

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

score 7 · Answer 7 · edited Sep 27 '13 at 09:18

7

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

edited Sep 27 '13 at 09:18

Jeromy Anglim

721
1
10
18

answered Feb 06 '11 at 22:53

Breathwork

79
1

Disagree with comments because of context. Someone with a superuser account who posts here is often the type to use vim. – uniquegeek Mar 01 '16 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. – Nigini Dec 16 '16 at 17:16

score 2 · Answer 8 · answered Feb 02 '11 at 16:12

2

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 02 '11 at 16:12

Duall

707
5
18

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

score 2 · Answer 9 · edited Oct 28 '20 at 19:37

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

score 1 · Answer 10 · edited Oct 28 '20 at 19:38

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

score 1 · Answer 11 · edited Mar 20 '17 at 10:17

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.

score 0 · Answer 12 · answered Nov 29 '19 at 07:33

0

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

answered Nov 29 '19 at 07:33

Arun Prasad E S

111
3

Hakan · Answer 13 · 2016-11-22T08:28:17.427

-1

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

edited Nov 22 '16 at 08:28

answered Nov 02 '16 at 12:45

Hakan

471
1
4
6

This is now at https://delim.co/ – Phil Dec 10 '18 at 19:53

Johnny · Answer 14 · 2011-02-03T11:39:22.040

-3

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

edited Feb 03 '11 at 11:39

answered Feb 02 '11 at 16:32

Johnny

875
1
8
18

5

This is an unscalable manual solution. – Daniel Hári Apr 07 '16 at 09:49

Convert a column into a comma separated list

14 Answers14

Linked

Related