CONCATENATE - maximum argument

  • Thread starter Thread starter Siva
  • Start date Start date
S

Siva

Hi

What is the maximum cells for an concatenate argument ?

I am trying to combine 300+ cells to one cell.
I have a if statement in the 300+ cells to show the data when the conditions
is met. So most of the 300+ cells are blank.

I am trying to put all the info from the 300+ cells that are populated into
1 cell.

Hope someone could help

Thanks
 
Maximum is 30.

This UDF will overcome that and ignore blank cells.

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 & " "
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function


Gord Dibben MS Excel MVP
 
Hi Gord

Thanks for your quick responce

Even if I use UDF, I can only use up to 30.

IS there a way where I can do a search or lookup of 300+ cells and transfer
only the populate cells to another section of the worksheet. So if there are
10 populated cells(out of 300), it will transfer the data to 10 separate
cells to the other section of the worksheet.

Then I can use concatenate on the 10 cells into 1 cells.

Please advice.

Thanks
 
If your range is contiguous, you could use Gord's UDF like:

=ConCatRange(a1:x9999)
 
Hi,

A little late but:

1. In 2007 CONCATENATE supports 255 arguments.
2. Instead of using the function you can use the operator &. I believe this
is limited by the maximum length of the formula which varies with version.

=A1&A2&A3...

Cheers,
Shane Devenshire
 
Back
Top