How to concatenate a range?

  • Thread starter Thread starter Nathan Gutman
  • Start date Start date
N

Nathan Gutman

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