CONCATENATE
 

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.

CONCATENATE
 

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.

CONCATENATE
 

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