Concatenate a range

[King]

How do you concatenate an entire range at once?

=CONCATENATE(A1:A10) entered as array still only returns
the first element, A1.

There has to be something better than -

=CONCATENATE(A1,A2,A3,A4,A5,...A10)

Thanks

 

[Jason Morin]

Use a simple UDF like this:

Function Concatall(rng As Range) As String
Dim cell As Range
For Each cell In rng
Concatall = Concatall & cell.Text
Next
End Function

---
Call as =Concatall(A1:A10)

HTH
Jason
Atlanta, GA

 

[King]

-----Original Message-----
Use a simple UDF like this:

Function Concatall(rng As Range) As String
Dim cell As Range
For Each cell In rng
Concatall = Concatall & cell.Text
Next
End Function

---
Call as =Concatall(A1:A10)

HTH
Jason
Atlanta, GA

.

That works. Thank you.

 

[Harlan Grove]

Jason Morin wrote...

Use a simple UDF like this:

Function Concatall(rng As Range) As String
Dim cell As Range
For Each cell In rng
Concatall = Concatall & cell.Text
Next
End Function

....

Or generalize,


Function mcat(ParamArray s()) As String
'Copyright (C) 2002, Harlan Grove
'This is free software. It's use in derivative works is covered
'under the terms of the Free Software Foundation's GPL. See
'http://www.gnu.org/copyleft/gpl.html
'------------------------------------
'string concatenation analog to SUM
Dim r As Range, x As Variant, y As Variant

For Each x In s
If TypeOf x Is Range Then
For Each r In x.Cells
mcat = mcat & r.Value
Next r
ElseIf IsArray(x) Then
For Each y In x
mcat = mcat & IIf(IsArray(y), mcat(y), y)
Next y
Else
mcat = mcat & x
End If
Next x
End Function


which allows expressions like

=mcat("hi",(A1:A4,A66,C2),"foo",A7:F9)