CONCATENATE

[kyoshirou]

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.

 

[T. Valko]

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

 

[Gord Dibben]

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