CONCATENATE - maximum argument

[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

 

[Gord Dibben]

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

 

[Siva]

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

 

[Dave Peterson]

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

=ConCatRange(a1:x9999)

 

[Gord Dibben]

Not true.

=concatrange(a1:a300) is what you need.


Gord

 

[Shane Devenshire]

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