How to concatenate a range?
 

To sum up a range I can use SUM(A1:A30) but cells A1:A30 contain text
(strings).
The function CONCATENATE(A1:A30) does work here.
Is there a simple way to that without having to use Range("A1") &
Range("A2") & ..........Range("A30")?
Thanks,

How to concatenate a range?
 

Nathan,

I think you need to roll your own. Something like

Function Concat(ParamArray V()) As String
Dim Ndx As Long
Dim R As Range
Dim S As String
For Ndx = LBound(V) To UBound(V)
If TypeOf V(Ndx) Is Excel.Range Then
For Each R In V(Ndx).Cells
S = S & R.Text
Next R
Else
S = S & Format(V(Ndx))
End If
Next Ndx
Concat = S
End Function

You can then call this from a worksheet as
=Concat(A1:A10)


--

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com






"Nathan Gutman" wrote in message
...
To sum up a range I can use SUM(A1:A30) but cells A1:A30
contain text
(strings).
The function CONCATENATE(A1:A30) does work here.
Is there a simple way to that without having to use Range("A1")
&
Range("A2") & ..........Range("A30")?
Thanks,

How to concatenate a range?
 

Thanks Chip, I was afraid of that.
On Thu, 18 Dec 2003 10:50:48 -0600, "Chip Pearson"
wrote:

Nathan,

I think you need to roll your own. Something like

Function Concat(ParamArray V()) As String
Dim Ndx As Long
Dim R As Range
Dim S As String
For Ndx = LBound(V) To UBound(V)
If TypeOf V(Ndx) Is Excel.Range Then
For Each R In V(Ndx).Cells
S = S & R.Text
Next R
Else
S = S & Format(V(Ndx))
End If
Next Ndx
Concat = S
End Function

You can then call this from a worksheet as
=Concat(A1:A10)