text from column to a list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to take a column of e-mail addresses and convert them into a list
seperated by a comma. Is there a way to do it? I have Excel 2000.
 
Try:

Sub Test()
Dim c As Range
Dim txt As String

txt = ""
For Each c In Selection.Cells
txt = txt & c.Value & ","
Next
txt = Left$(txt, Len(txt) - 1)
'Do whatever you want with txt
MsgBox txt
Set c = Nothing
End Sub

Regards,
Greg
 
Hi,

I always use this, works horizontal and vertical range.

Jack Sons.


'**************************************************************************
'Purpose: Merge cells, retaining all data
'Inputs: Selection
'Returns: Merged values in the first cell of rRng
'**************************************************************************

' Merging the Selection INTO ONE CELL
'This macro merges the entire Selection into one cell. To include a
delimiter,
'change SDELIM, or insert columns between the cells and put the delimiter
'in those columns (make sure the last column is a delimiter column as well.

Public Sub MergeToOneCell()
'J.E. McGimpsey, http://www.mcgimpsey.com/excel/mergedata.html
Dim sDELIM As String 'Const sDELIM As String = ", "
sDELIM = InputBox("Input one or more characters for delimiter")
Dim rCell As Range
Dim sMergeStr As String
With Selection
For Each rCell In .Cells
sMergeStr = sMergeStr & sDELIM & rCell.text
Next rCell
Application.DisplayAlerts = False
.Merge Across:=False
Application.DisplayAlerts = True
.Item(1).Value = Mid(sMergeStr, 1 + Len(sDELIM))
End With
Selection.UnMerge
ActiveCell.Select
End Sub
 
Back
Top