Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate contiguous fields on one worksheet into 1 field on ano
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate contiguous fields on one worksheet into 1 field on ano
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate contiguous fields on one worksheet into 1 field on
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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I concatenate two date fields | Excel Discussion (Misc queries) | |||
Format date and time from one contiguous field | Excel Discussion (Misc queries) | |||
copy field settings of a field to other fields in same pivot tbl | Excel Worksheet Functions | |||
clear contiguous fields with one keystroke | Excel Programming | |||
How to Join/concatenate a date field with a time field in Excel? | Excel Discussion (Misc queries) |