combine rows into one cell separated by comma

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.

hi Miki Jo

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Miki Jo" <Miki Jo@discussions.microsoft.com> wrote in message news:20F52427-1521-41E5-9A2C-7F36BA7994A9@microsoft.com...
> 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.

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

On Thu, 9 Mar 2006 09:41:29 -0800, Miki Jo <Miki Jo@discussions.microsoft.com>
wrote:

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