How do I create a comma seperated list in excel?

Favorite Answer

It sounds as if you need a single cell Array formula that will combine the non-blank values into a single text string, with each array value separated by a comma.

And that would be easy to do if the CONCATENATE function allowed for the use of Array values, but Microsoft never has done this. If they had, the formula would be something like this:

= CONCATENATE( IF( ISBLANK( E1:E10 ), "", E1:E10 & "," ) )

To actually get the above to work, you will need to add your own VBA macro (the code follows at the end). Here is the syntax for this macro function:

StrgCncat( Sep, SkipEmpty, ArrayArgs )

• Where the "Sep" parameter is a character or characters that separate the strings being concatenated. This may be 0 or more characters. The Sep parameter is required. If you do not want any separators in the result string, use an empty string for the value of Sep. The Sep value appears between each string element being concatenated, but does not appear at either the beginning or end of the result string.

• Where the "SkipEmpty" parameter indicates whether empty element in the Args array are to be skipped. If this value is False, there may be consecutive Sep characters indicating empty values. For example, =StrgCncat(A1:A5) might return 1,2,,4,5 if cell A3 is empty. If this parameter is True, empty elements will be skipped and there will be no consecutive delimiters. It is important to distinguish between an empty value and a string value that contains a space character, Chr(32). The SkipEmpty parameter, if True, will cause empty values to be ignored, but text that contains only a space character will be included.

• Where the "ArrayArgs" array contains the strings elements to be concatenated. Each element in the Args array can be one of the following:
° ° ° A string within quotes or a number. For exampe, "A" or 123.
° ° ° A single cell reference. For example, A10.
° ° ° A range of cells. For example, A1:B10.
° ° ° A literal array constant. For example, {1,2,"A","B","C"}.

Function StrgCncat(Sep As String, SkipEmpty As Boolean, ParamArray Args()) As String
Dim S As String
Dim N As Long
Dim M As Long
Dim R As Range
Dim NumDims As Long
Dim LB As Long
Dim IsArrayAlloc As Boolean
Dim RN As Long
Dim CN As Long
If UBound(Args) - LBound(Args) + 1 = 0 Then
StrgCncat = vbNullString
Exit Function
End If
For N = LBound(Args) To UBound(Args)
If IsObject(Args(N)) = True Then
If TypeOf Args(N) Is Excel.Range Then
For Each R In Args(N).Cells
If SkipEmpty = True Then
If R.Text <> vbNullString Then
S = S & R.Text & Sep
End If
Else
S = S & R.Text & Sep
End If
Next R
Else
StrgCncat = CVErr(xlErrValue)
Exit Function
End If
ElseIf IsArray(Args(N)) = True Then
On Error Resume Next
IsArrayAlloc = (Not IsError(LBound( Args(N) )) And _
(LBound( Args(N) ) <= UBound( Args(N) )))
On Error GoTo 0
If IsArrayAlloc = True Then
NumDims = 1
On Error Resume Next
Err.Clear
NumDims = 1
Do Until Err.Number <> 0
LB = LBound(Args(N), NumDims)
If Err.Number = 0 Then
NumDims = NumDims + 1
Else
NumDims = NumDims - 1
End If
Loop
If NumDims > 2 Then
StrgCncat = CVErr(xlErrValue)
Exit Function
End If
If NumDims = 1 Then
For M = LBound(Args(N)) To UBound(Args(N))
If Args(N)(M) <> vbNullString Then
If SkipEmpty = True Then
If Args(N)(M) <> vbNullString Then
S = S & Args(N)(M) & Sep
End If
Else
S = S & Args(N)(M) & Sep
End If
End If
Next M
Else
For RN = LBound(Args(N), 1) To UBound(Args(N), 1)
For CN = LBound(Args(N), 2) To UBound(Args(N), 2)
If SkipEmpty = True Then
If Args(N)(RN, CN) <> vbNullString Then
S = S & Args(N)(RN, CN) & Sep
End If
Else
S = S & Args(N)(RN, CN) & Sep
End If
Next CN
Next RN
End If
Else
S = S & Args(N) & Sep
End If
Else
If SkipEmpty = True Then
If Args(N) <> vbNullString Then
S = S & Args(N) & Sep
End If
Else
S = S & Args(N) & Sep
End If
End If
Next N
If Len(Sep) > 0 Then
S = Left(S, Len(S) - Len(Sep))
End If
StrgCncat = S
End Function...Show more

If you have only around 200 email addresses................... Select that range & Copy Paste Special - Transpose Now copy that entire row Open word and paste it Select that table Go to Table Menu Convert - Table to text Select "other" and put a comma "," in that box OK...Show more

If what you want to do is get rid of empty rows, try this..

a couple of options.

1. Use "Autofilter"
- Select the entire sheet
- Data --> Auto Filter
- Select "Blanks" from the drop down list/s
- Select all the rows (except the one with drop down list
- Press Crtl+G to open "Go to" dialog box
- Click "Special"
- Select "Visible Cells only"
- Click "Ok"
- Right Click & Select "Delete" -- "Entire Rows"
- Remove "Auto Filter"

2. Sort & delete
- Insert a new column & number it (1,2,3,,,to the end of the list)
- Select entire sheet
- Go to Data - Sort
- Sort by any column/s other than the one you just inserted.. This should move all your blank rows to the bottom of the list/table/data range
- Select all of those blank rows & delete them
- finally sort your data by the number column in order to bring it to its original order without blanks

3. Use a macro..
http://www.ozgrid.com/VBA/VBACode.htm...Show more

You could use the Merge function in ASAP Utilities. This works like CONCATENATE except you can choose an array of cells instead of doing a max of 30. ASAP utilities is a great free add-in to all versions of Excel and you can download it here: http://www.asap-utilities.com/download-asap-utilities.php

Before you run the merge, sort the sheet in ascending order and delete rows that are blank....Show more