combine rows into one cell separated by comma

[Guest]

Need to combine 100 email addesses in separate rows (in Excel) into one cell,
separated by comma, so I can do mass mail. Formula I'm trying won't work.

 

[Ron de Bruin]

hi Miki Jo

You can use code or a function like this to do it
http://www.mcgimpsey.com/excel/udfs/multicat.html

 

[Gord Dibben]

Miki

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) > 0 Then sbuf = sbuf & cell.text & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

usage would be =ConCatRange(A1:A100)


Gord Dibben MS Excel MVP