reformatting for labels
 

I exported a mailing list file from a database into an excel
spreadsheet.
The problem is the data separated into one line = one cell in the
column, such as

R1C1 John Doe
R2C1 300 Main Street
R3C1 Anytown, USA 66666

I need to get all three rows into one row.
There is a blank row between each record.

I expect I need to grab rows 2 and 3 and add them to row 1, but
keeping the data in 3 lines, not in one long line in the cell.

I guess that the blank row can stay as is - I'll know more about this
when I start to use the data.

I tried to do this with a macro but it only wants to deal with one
cell at a time. I tried typing the second line in at the data input
box but I don't know how to get it to take the data and split it into
3 lines of data in one cell.

Could someone please help me with this. I am familiar with VBA in
MSWord and MSAccess and a bit in Outlook, but this is my first stab at
it in Excel.

My appreciation for your time and expertise
Joanne

reformatting for labels
 

Use vbLF "Line Feed" to manipulate this. For example:
ActiveCell = ActiveCell & vbLF & ActiveCell.Offset(1,0) & vbLF &
Activecell.Offset(2,0)
I am guessing that you can figure out the looping on your own. Let me
know if you need more info

Die_Another_Day
Joanne wrote:
I exported a mailing list file from a database into an excel
spreadsheet.
The problem is the data separated into one line = one cell in the
column, such as

R1C1 John Doe
R2C1 300 Main Street
R3C1 Anytown, USA 66666

I need to get all three rows into one row.
There is a blank row between each record.

I expect I need to grab rows 2 and 3 and add them to row 1, but
keeping the data in 3 lines, not in one long line in the cell.

I guess that the blank row can stay as is - I'll know more about this
when I start to use the data.

I tried to do this with a macro but it only wants to deal with one
cell at a time. I tried typing the second line in at the data input
box but I don't know how to get it to take the data and split it into
3 lines of data in one cell.

Could someone please help me with this. I am familiar with VBA in
MSWord and MSAccess and a bit in Outlook, but this is my first stab at
it in Excel.

My appreciation for your time and expertise
Joanne

reformatting for labels
 

Hi Joanne,

Not sure if you want the three lines in one cell or a row of 3 columns

If this is a one off, for the time it takes to figure some suggested code
and adapt to your scenario probably quicker to do manually.

Start with your data in cell B1 down
A1: =MOD(ROW(),4)
Double click the handle bottom right to copy down
Copy this column of formulas
PasteSpecial Values back over the formulas
Sort Cols A:B on col A
I expect you get the idea now
Delete the empty 0 rows
Copy the 2''s data next to the 1's in col-C, the 3's data to Col-D
If necessary -
E1: = B1 & ", " & C1 & ", "D1
Copy down
Copy and pastespecial back values

Or if you want three lines in each cell, separate with
&","&char(10)&
Format Alignment Wrap text
Autofit Row & Col widths

Regards,
Peter T

"Joanne" wrote in message
...
I exported a mailing list file from a database into an excel
spreadsheet.
The problem is the data separated into one line = one cell in the
column, such as

R1C1 John Doe
R2C1 300 Main Street
R3C1 Anytown, USA 66666

I need to get all three rows into one row.
There is a blank row between each record.

I expect I need to grab rows 2 and 3 and add them to row 1, but
keeping the data in 3 lines, not in one long line in the cell.

I guess that the blank row can stay as is - I'll know more about this
when I start to use the data.

I tried to do this with a macro but it only wants to deal with one
cell at a time. I tried typing the second line in at the data input
box but I don't know how to get it to take the data and split it into
3 lines of data in one cell.

Could someone please help me with this. I am familiar with VBA in
MSWord and MSAccess and a bit in Outlook, but this is my first stab at
it in Excel.

My appreciation for your time and expertise
Joanne