I have a column in a worksheet containing 110 rows, each of which has a
single email address. I would like to concatenate the contents of all
110 cells, each one separated from the next by a comma to be used as a
single email address. Is there an easier way to do this other than to
write a formula in which I have to identify each and every cell to be
included in the concatenation?


--
nebb
------------------------------------------------------------------------
nebb's Profile: http://www.excelforum.com/member.php...fo&userid=8981
View this thread: http://www.excelforum.com/showthread...hreadid=492425

I don't know if you'll be able to contain 110 email addresses in a single
string, but in general I deal with similar situations with a helper column.
Suppose your addresses are in B2:B111. In C2, enter =b2. In C3, enter =c2 &
", " & b3. Copy the formula from C3 to C4:C111. C111 will have your
complete list.

"nebb" wrote:


I have a column in a worksheet containing 110 rows, each of which has a
single email address. I would like to concatenate the contents of all
110 cells, each one separated from the next by a comma to be used as a
single email address. Is there an easier way to do this other than to
write a formula in which I have to identify each and every cell to be
included in the concatenation?


--
nebb
------------------------------------------------------------------------
nebb's Profile: http://www.excelforum.com/member.php...fo&userid=8981
View this thread: http://www.excelforum.com/showthread...hreadid=492425

nebb

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox _
("Select Cells...Contiguous or Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) 0 Then sbuf = sbuf & y.text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub

Alternative.............UDF

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

=ConCatRange(A1:A110)


Gord Dibben Excel MVP

On Sat, 10 Dec 2005 09:55:07 -0600, nebb
wrote:


I have a column in a worksheet containing 110 rows, each of which has a
single email address. I would like to concatenate the contents of all
110 cells, each one separated from the next by a comma to be used as a
single email address. Is there an easier way to do this other than to
write a formula in which I have to identify each and every cell to be
included in the concatenation?

"Gord Dibben" <gorddibbATshawDOTca@ wrote...
....
Alternative.............UDF

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

=ConCatRange(A1:A110)
....

Generality and flexibility are good. Also a little trickery goes a long way.

http://groups.google.com/group/micro...e=source&hl=en

(or http://makeashorterlink.com/?S1E33459B ) includes a function named mcat
that takes a variable number of arguments, like SUM. It could be used in

=SUBSTITUTE(TRIM(MCAT(" "&YourRangeAddressHere))," ",",")

As for trickery, much better in VBA to append the field separator between
sbuf and the next entry, so

sbuf = sbuf & "," & Cell.text

which puts an extraneous comma at the beginning of sbuf. It's easier and
more efficient then to use

ConCatRange = Mid$(sbuf, 2)