Hello,

Can i ask when i used CONCATENATE, how many fields can i CONCATENATE up to?
For example, i can only CONCATENATE from E2,E3,E4,.... to AH..

if i continue to CONCATENATE AI onwards, excel prompt me error.

How do i solve it? i would like to further CONCATENATE till AZ.

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,",")


--
Biff
Microsoft Excel MVP


"kyoshirou" wrote in message
...
Hello,

Can i ask when i used CONCATENATE, how many fields can i CONCATENATE up
to?
For example, i can only CONCATENATE from E2,E3,E4,.... to AH..

if i continue to CONCATENATE AI onwards, excel prompt me error.

How do i solve it? i would like to further CONCATENATE till AZ.

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,",")