Or use this UDF

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) 0 Then sbuf = sbuf & cell.text & " " 'use "" if no
'space wanted
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

=concatrange(E2:AZ2)


Gord Dibben MS Excel MVP

On Sun, 2 Mar 2008 23:21:26 -0500, "T. Valko" wrote:

The argument limit to CONCATENATE is 30 in versions prior to Excel 2007. The
limit in Excel 2007 is 255.

Sounds like you're *not* using Excel 2007.

Try it like this:

=E2&E3&E4&E5&E6&E7

OR, if you want spaces between items:

=E2&" "&E3&" "&E4&" "&E5

However, this can result in a really long formula. An alternative is to
download the *free* Morefunc.xll add-in from:

http://xcell05.free.fr/english/

Then use this formula:

=MCONCAT(E2:AZ2,"d")

Where d = the delimiter, if any, that you want to use. For example, to use a
comma delimiter:

=MCONCAT(E2:AZ2,",")