Concatenate question

I have a dynamic list entered in columns. I am trying to take one of those
columns and concatenate it into a single cell to be copied directly to a
separate form in the format required. My problem is that the list is dynamic
in both the number of records and the data. It is impractical to constantly
modify the concatenate statement that provides the final product to copy and
paste into my form. Is there any way to concatenate an entire column into
one cell like this dynamically?

Nick

Can you work with a User Defined Function?

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

You can enter a range like =ConCatRange(A:A)

The UDF will not return blank cells and when cells are inserted or deleted, the
results will reflect that.

As written, a comma is inserted between each cell value.


Gord Dibben MS Excel MVP

On Thu, 27 Jul 2006 14:27:02 -0700, Nick <[email protected]> wrote:

>I have a dynamic list entered in columns. I am trying to take one of those
>columns and concatenate it into a single cell to be copied directly to a
>separate form in the format required. My problem is that the list is dynamic
>in both the number of records and the data. It is impractical to constantly
>modify the concatenate statement that provides the final product to copy and
>paste into my form. Is there any way to concatenate an entire column into
>one cell like this dynamically?

Not knowing the nature of your lists, I'll hazard this.

You could put this in, say, A1:

=A2&A3&A4&A5&A6

This will create a string in A1 that contains the values of the cells
below. Blank cells, and cells that evaluate to blank, don't add to the
string. Add more arguments if needed. If you have other columns, copy
the formula to those columns.

If you have a large number of possible rows this could get unwieldy,
and I'm not sure how many arguments you can use.

If you need to add punctuation or other symbols and know where these
belong, you might have something like:

=A2&"-"&A3&A4&", "&A5&A6

Hope this helps.


Nick wrote:
> I have a dynamic list entered in columns. I am trying to take one of those
> columns and concatenate it into a single cell to be copied directly to a
> separate form in the format required. My problem is that the list is dynamic
> in both the number of records and the data. It is impractical to constantly
> modify the concatenate statement that provides the final product to copy and
> paste into my form. Is there any way to concatenate an entire column into
> one cell like this dynamically?

Gord, you have alleviated hours of frustration on this issue for me. Thank
you.

"Gord Dibben" wrote:

> Nick
>
> Can you work with a User Defined Function?
>
> 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
>
> You can enter a range like =ConCatRange(A:A)
>
> The UDF will not return blank cells and when cells are inserted or deleted, the
> results will reflect that.
>
> As written, a comma is inserted between each cell value.
>
>
> Gord Dibben MS Excel MVP
>
> On Thu, 27 Jul 2006 14:27:02 -0700, Nick <[email protected]> wrote:
>
> >I have a dynamic list entered in columns. I am trying to take one of those
> >columns and concatenate it into a single cell to be copied directly to a
> >separate form in the format required. My problem is that the list is dynamic
> >in both the number of records and the data. It is impractical to constantly
> >modify the concatenate statement that provides the final product to copy and
> >paste into my form. Is there any way to concatenate an entire column into
> >one cell like this dynamically?
>
>