How to combine email addresses in one cell?

  • Thread starter Thread starter Amit
  • Start date Start date
A

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
 
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
 
Back
Top