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

--
Siva

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

On Thu, 28 Aug 2008 07:14:01 -0700, Siva <Siva@discussions.microsoft.com>
wrote:

>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

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
--
Siva


"Gord Dibben" wrote:

> 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
>
> On Thu, 28 Aug 2008 07:14:01 -0700, Siva <Siva@discussions.microsoft.com>
> wrote:
>
> >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
>
>

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

=ConCatRange(a1:x9999)



Siva wrote:
>
> 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
> --
> Siva
>
> "Gord Dibben" wrote:
>
> > 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
> >
> > On Thu, 28 Aug 2008 07:14:01 -0700, Siva <Siva@discussions.microsoft.com>
> > wrote:
> >
> > >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
> >
> >

--

Dave Peterson

Not true.

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


Gord

On Thu, 28 Aug 2008 07:38:01 -0700, Siva <Siva@discussions.microsoft.com>
wrote:

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

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

"Siva" <Siva@discussions.microsoft.com> wrote in message
news:F90144D8-734B-40F9-A056-DDA1B46E5BFE@microsoft.com...
> 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
>
> --
> Siva