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,A6:D6,C2),"foo",A7:F9)