How to combine email addresses in one cell?

[Amit]

Hi,

I have an Excel Worksheet with names and email addresses
in separate columns.

I would like to get a simple text file with all the email
addresses separated by a comma or a semi-colon.

I know how to split a cell into multiple columns, but not
sure how to go about doing the reverse - that is
combining/concatenating all the cells for all the rows in
a column. I looked at "Concatenate" function, but I have
hundreds of rows, and I'm not sure how I can specify the
range without typing it all in. Plus, I also need to
insert a comma between email addresses.

Any help or pointers will be appreciated.

Thanks!

-Amit

 

[Bernie Deitrick]

Amit,

Try the sub below, after first selecting all the cells with your addresses.
It will generate the text file Address.txt in your default folder. My
assumptions are that your address cells are contiguous and none are blank.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim FileNumOut As Integer
Dim WholeLine As String
Dim i As Integer

FileNumOut = FreeFile()
Open "Address.txt" For Output Access Write As FileNumOut

WholeLine = Selection.Cells(1).Value
For i = 2 To Selection.Cells.Count
WholeLine = WholeLine & "," & Selection.Cells(i).Value
Next i

Print #FileNumOut, WholeLine
Close FileNumOut
End Sub