Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Word address labels to Excel Columns
How would I copy a list of address labels into excel and then get the names,
street addresses and city/state/zip into three seprate columns so as to be able to sort, etc. without copying the individual label info one at a time (I have 1800)? When I copy them into Excel, it lists XYZ name in A1, Street into A2, City into A3. I want A, B, C without having to do them one at a time. Any suggestions? I'm sorry if this question is too poorly written to understand... |
#2
|
|||
|
|||
Any response is gonna depend on what your data looks like.
Are there always x number of rows per group? Is row 1 of the group always the same field Same with 2 through X. If there aren't always the same number of rows per group, is there a gap between groups? If there aren't always the same number of rows per group, how do you know which row of the group goes into what column of the flattened row? jlbowman wrote: How would I copy a list of address labels into excel and then get the names, street addresses and city/state/zip into three seprate columns so as to be able to sort, etc. without copying the individual label info one at a time (I have 1800)? When I copy them into Excel, it lists XYZ name in A1, Street into A2, City into A3. I want A, B, C without having to do them one at a time. Any suggestions? I'm sorry if this question is too poorly written to understand... -- Dave Peterson |
#3
|
|||
|
|||
There are always four rows to each group. Example: A1 is name; A2 is
Company; A3 is Street Address, A4 is city,state&zip, A5 is blank, A6 is name2, A7 is Company of name2, A8 is Street Address of Name2, A9 is city,state&zip of name2, a10 is blank, etc... "Dave Peterson" wrote: Any response is gonna depend on what your data looks like. Are there always x number of rows per group? Is row 1 of the group always the same field Same with 2 through X. If there aren't always the same number of rows per group, is there a gap between groups? If there aren't always the same number of rows per group, how do you know which row of the group goes into what column of the flattened row? jlbowman wrote: How would I copy a list of address labels into excel and then get the names, street addresses and city/state/zip into three seprate columns so as to be able to sort, etc. without copying the individual label info one at a time (I have 1800)? When I copy them into Excel, it lists XYZ name in A1, Street into A2, City into A3. I want A, B, C without having to do them one at a time. Any suggestions? I'm sorry if this question is too poorly written to understand... -- Dave Peterson |
#4
|
|||
|
|||
Sub ColtoRows_NoError()
Dim Rng As Range Dim i As Long Dim j As Long Dim nocols As Integer Application.ScreenUpdating = False Set Rng = Cells(Rows.Count, 1).End(xlUp) j = 1 On Error Resume Next nocols = InputBox("Enter Number of Columns Desired") For i = 1 To Rng.Row Step nocols Cells(j, "A").Resize(1, nocols).Value = _ Application.Transpose(Cells(i, "A").Resize(nocols, 1)) j = j + 1 Next Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents Application.ScreenUpdating = True End Sub When asked "how many columns" enter 5 to allow for the blank row between each set of 4. You can then delete the Company column if you so choose. You made no mention of it appearing in the amended layout. Gord Dibben Excel MVP On Wed, 9 Feb 2005 04:23:09 -0800, "jlbowman" wrote: There are always four rows to each group. Example: A1 is name; A2 is Company; A3 is Street Address, A4 is city,state&zip, A5 is blank, A6 is name2, A7 is Company of name2, A8 is Street Address of Name2, A9 is city,state&zip of name2, a10 is blank, etc... "Dave Peterson" wrote: Any response is gonna depend on what your data looks like. Are there always x number of rows per group? Is row 1 of the group always the same field Same with 2 through X. If there aren't always the same number of rows per group, is there a gap between groups? If there aren't always the same number of rows per group, how do you know which row of the group goes into what column of the flattened row? jlbowman wrote: How would I copy a list of address labels into excel and then get the names, street addresses and city/state/zip into three seprate columns so as to be able to sort, etc. without copying the individual label info one at a time (I have 1800)? When I copy them into Excel, it lists XYZ name in A1, Street into A2, City into A3. I want A, B, C without having to do them one at a time. Any suggestions? I'm sorry if this question is too poorly written to understand... -- Dave Peterson |
#5
|
|||
|
|||
Assuming your data starts in A1, put this formula in B1 and copy across
through E1. Then copy B1:E1 down until the formulas begin to show 0's. =OFFSET($A$1,(ROW()-1)*5+COLUMN()-2,0) Once you've done that, you can Edit/Copy the formulas, then Edit/Paste Special and select the Values option to convert formulas to their values. Then you can delete the original column A if you like. To get rid of cells containing 0 (which represent blank cells in the original list) you can use search and replace. If you need to separate City, state, and ZIP into separate columns, you can perhaps use Data/Text to Columns, but if you use a space as the delimiter and you have cities and/or states with multiple-word names (e.g. San Antonio, North Carolina), you will have problems and might need a macro. On Wed, 9 Feb 2005 04:23:09 -0800, "jlbowman" wrote: There are always four rows to each group. Example: A1 is name; A2 is Company; A3 is Street Address, A4 is city,state&zip, A5 is blank, A6 is name2, A7 is Company of name2, A8 is Street Address of Name2, A9 is city,state&zip of name2, a10 is blank, etc... "Dave Peterson" wrote: Any response is gonna depend on what your data looks like. Are there always x number of rows per group? Is row 1 of the group always the same field Same with 2 through X. If there aren't always the same number of rows per group, is there a gap between groups? If there aren't always the same number of rows per group, how do you know which row of the group goes into what column of the flattened row? jlbowman wrote: How would I copy a list of address labels into excel and then get the names, street addresses and city/state/zip into three seprate columns so as to be able to sort, etc. without copying the individual label info one at a time (I have 1800)? When I copy them into Excel, it lists XYZ name in A1, Street into A2, City into A3. I want A, B, C without having to do them one at a time. Any suggestions? I'm sorry if this question is too poorly written to understand... -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste table from excel to word | Excel Worksheet Functions | |||
moving excel in word back to excel | Excel Discussion (Misc queries) | |||
How can I import addresses in Excel over to address labels in Wor. | Excel Discussion (Misc queries) | |||
Add more lines and more columns in Excel | Excel Worksheet Functions | |||
Can you print labels using Excel 2002 in a Word 2002 mail merge? | Excel Discussion (Misc queries) |