Word address labels to Excel Columns

[Guest]

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]

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?

 

[Guest]

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...

 

[Gord Dibben]

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

 

[Myrna Larson]

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.