Concatenate shortcut?

[nebb]

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

 

[bpeltzer]

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.

 

[Gord Dibben]

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

 

[Harlan Grove]

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/micr...tions/msg/94456a9e326b19a6?dmode=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)