On Sat, 15 Oct 2005 21:14:02 -0700, Ark wrote:

I would like to know if there is a way to concatenate multiple cells in a row
without having to use concatenate with "," or "&" ?

i.e. is there something like concatenate(A1:F1)?

You could use a UDF.

The one below puts n Spaces between each string. It will handle a contiguous
cell range, non-contiguous cell ranges, or mixed ranges and strings as an
argument. For example:

=setstring(1,A1:D1,G1:H1,"the end")

would be a valid function call.

========================
Function SetString(SpacesBetween As Integer, _
ParamArray rg() As Variant) As String

'by Ron Rosenfeld

Dim c As Variant
Dim i As Long

For i = 0 To UBound(rg)
Select Case VarType(rg(i))
Case Is = vbArray + vbVariant
For Each c In rg(i)
SetString = SetString & Space(SpacesBetween) & Trim(c.Text)
Next
Case Is = vbString
SetString = SetString & Space(SpacesBetween) & Trim(rg(i))
End Select
Next i

SetString = Trim(SetString)

End Function
====================


--ron