I have an Excel workbook that has 2 worksheets. One worksheet (called Master)
has company and address information in separate, contiguous cells. I need to
concatenate those cells into a single cell on the other worksheet, with line
breaks between.

Example:
Company Name
Address Line 1
Address Line 2
City, ST Zipcode

Some addresses will not have the Address Line 2, and I don't want to show a
blank line.

Thanks

Function ConCatRange(CellBlock As Range) As String
'ignores blank cells
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.Text) > 0 Then sbuf = sbuf & Cell.Text & vbLf
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

=concatrange(Master!A1:A4)


Gord Dibben MS Excel MVP

On Mon, 5 Oct 2009 14:30:01 -0700, MargeB <(E-Mail Removed)>
wrote:

>I have an Excel workbook that has 2 worksheets. One worksheet (called Master)
>has company and address information in separate, contiguous cells. I need to
>concatenate those cells into a single cell on the other worksheet, with line
>breaks between.
>
>Example:
>Company Name
>Address Line 1
>Address Line 2
>City, ST Zipcode
>
>Some addresses will not have the Address Line 2, and I don't want to show a
>blank line.
>
>Thanks

Thanks for the reply. I haven't had a chance to try it yet.

"Gord Dibben" wrote:

> Function ConCatRange(CellBlock As Range) As String
> 'ignores blank cells
> 'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
> Dim Cell As Range
> Dim sbuf As String
> For Each Cell In CellBlock
> If Len(Cell.Text) > 0 Then sbuf = sbuf & Cell.Text & vbLf
> Next
> ConCatRange = Left(sbuf, Len(sbuf) - 1)
> End Function
>
> =concatrange(Master!A1:A4)
>
>
> Gord Dibben MS Excel MVP
>
> On Mon, 5 Oct 2009 14:30:01 -0700, MargeB <(E-Mail Removed)>
> wrote:
>
> >I have an Excel workbook that has 2 worksheets. One worksheet (called Master)
> >has company and address information in separate, contiguous cells. I need to
> >concatenate those cells into a single cell on the other worksheet, with line
> >breaks between.
> >
> >Example:
> >Company Name
> >Address Line 1
> >Address Line 2
> >City, ST Zipcode
> >
> >Some addresses will not have the Address Line 2, and I don't want to show a
> >blank line.
> >
> >Thanks
>
>