How do I create a comma seperated list in excel?

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

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

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

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

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