need to concatenate 400 rows, into one cell separated by commas

[fiuPikeOY]

Hello,

I have a spreadsheet with 400+ rows (only one column) of email address, I need to put a comma between all of those emails to be able to paste them onto the to: field. So how can I concatenate 400 rows, and put a comma inbetween each record?

Thanks in advance

 

[xlhelp]

Save it as CSV file. Open in Word. Replace all paragraph marks with a comma.

http://www.geniusmedia.ca

 

[BenRowe]

Copy and then paste unformatted in word. Search and replace para marks ("^p") with ", ".

 

[byundt]

Here's a user defined function that will concatenate all the cells in a range, separated by a character of your choosing. Install this function in a regular module sheet, then use it with a worksheet formula like:
=Concat(A1:A400,",")

Function Concat(rg As Range,sSeparator as string) As String
Dim i As Long, nRows As Long
Dim x As Variant, y As Variant
x = rg.Value
nRows = UBound(x)
ReDim y(1 To nRows)
For i = 1 To nRows
    y(i) = x(i, 1)
Next
Concat = Join(y, sSeparator)
End Function

Brad