Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reformatting | Excel Discussion (Misc queries) | |||
Help With Reformatting | Excel Discussion (Misc queries) | |||
reformatting dates | Excel Discussion (Misc queries) | |||
reformatting data | Excel Programming | |||
Reformatting a cell using VBA | Excel Programming |