How do I reformat my address book

[suicidalporcupine]

My boss handed me a notepad text filled with address when I placed it on excel it looked like they all went on column A like so:[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Company's Name[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Address[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company's Name[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Address[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I want to fix it so that the Name and Address would go to B and C
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Company's Name[/TD]
[TD]Name[/TD]
[TD]Address[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Then he wants me to print it all out probably with Microsoft words in Envelops (Mailing Labels)

 

[Special-K99]

Try this
Input in Sheet1 column B
Output in Sheet 2

'
'   Convert Sheet1 single column to table with width m
'
Sub SingleColumnWidth()
m = 3
k = 1
l = 0
Application.ScreenUpdating = False
Lastrow = Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
For i = 1 To Lastrow
LastCol = 2
For j = 2 To LastCol
l = l + 1
Debug.Print Worksheets("Sheet1").Cells(i, j)
Worksheets("Sheet2").Cells(k, l) = Worksheets("Sheet1").Cells(i, j)
If l = m Then k = k + 1: l = 0
Next j
Next i
Application.ScreenUpdating = True
End Sub

 

[JoeMo]

Assuming your data are in col A starting in A1 as shown below, this will rearrange the data and place them in cols C:E as shown below.

Excel Workbook
	A	B	C	D	E
1	Company1		Company Name	Name	Address
2	Name1		Company1	Name1	Address1
3	Address1		Company2	Name2	Address2
4	Company2		Company3	Name3	Address3
5	Name2		Company4	Name4	Address4
6	Address2		Company5	Name5	Address5
7	Company3		Company6	Name6	Address6
8	Name3		Company7	Name7	Address7
9	Address3		Company8	Name8	Address8
10	Company4		Company9	Name9	Address9
11	Name4		Company10	Name10	Address10
12	Address4
13	Company5
14	Name5
15	Address5
16	Company6
17	Name6
18	Address6
19	Company7
20	Name7
21	Address7
22	Company8
23	Name8
24	Address8
25	Company9
26	Name9
27	Address9
28	Company10
29	Name10
30	Address10
31	Company11
32	Name11
33	Address11
Sheet3

Sub RearrangeCompanyInfo()
Const Rws As Long = 3
Dim Vin As Variant, i As Long, Vout As Variant, j As Long
Application.ScreenUpdating = False
Range("C1:E1").Value = Array("Company Name", "Name", "Address")
Vin = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value
ReDim Vout(1 To UBound(Vin, 1), 1 To Rws)
i = 1
Do
    j = j + 1
    Vout(j, 1) = Vin(i, 1)
    Vout(j, 2) = Vin(i + 1, 1)
    Vout(j, 3) = Vin(i + 2, 1)
    i = i + Rws
Loop While i < UBound(Vin, 1) - Rws
Range("C2:E" & j + 1).Value = Vout
Columns("C:E").AutoFit
Application.ScreenUpdating = True
End Sub

 

[suicidalporcupine]

THANK YOU!!! THIS WORK PERFECTLY.

but there's another format and this is one is weird.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1, Company's Name
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PO BOX 343432
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oakland CA 94108
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2, Company's Name, PO BOX 343432
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oakland CA 94108
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3, Company's Name, Name
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PO BOX 343432
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oakland CA 94108
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Want to change the same format as
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]COMPANY'S NAME
[/TD]
[TD]ADDRESS
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[suicidalporcupine]

Assuming your data are in col A starting in A1 as shown below, this will rearrange the data and place them in cols C:E as shown below.
Sheet3

Thanks JoeMo This is very helpful

but Can you also check my other list? is it possible to format the same as the first list?

 

[JoeMo]

Thanks JoeMo This is very helpful

but Can you also check my other list? is it possible to format the same as the first list?

Too much variation in the format of the raw data to anything reasonably simplistic with it.

 

[suicidalporcupine]

Too much variation in the format of the raw data to anything reasonably simplistic with it.

then how about if I ask excel to make all into 1 single line.

All the Client mailing address starts with 1, 2, 3, ......5000, so can it look like:

1, Company's name... PO BOX ,... SF CA
2, Company's name... PO BOX ,... SF CA
etc

From this format I can then use text to column and separate them accordingly?

 

[JoeMo]

then how about if I ask excel to make all into 1 single line.

All the Client mailing address starts with 1, 2, 3, ......5000, so can it look like:

1, Company's name... PO BOX ,... SF CA
2, Company's name... PO BOX ,... SF CA
etc

From this format I can then use text to column and separate them accordingly?

The problem is not with the format of the output, its with the variable formats in the input. I'm not saying it can't be done, but to me it looks like a lot more effort than I care to devote to it.