How do I reformat my address book

suicidalporcupine

Board Regular
Joined
Apr 1, 2015
Messages
90
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)
 
Try this
Input in Sheet1 column B
Output in Sheet 2

Code:
'
'   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
 
Upvote 0
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
ABCDE
1Company1Company NameNameAddress
2Name1Company1Name1Address1
3Address1Company2Name2Address2
4Company2Company3Name3Address3
5Name2Company4Name4Address4
6Address2Company5Name5Address5
7Company3Company6Name6Address6
8Name3Company7Name7Address7
9Address3Company8Name8Address8
10Company4Company9Name9Address9
11Name4Company10Name10Address10
12Address4
13Company5
14Name5
15Address5
16Company6
17Name6
18Address6
19Company7
20Name7
21Address7
22Company8
23Name8
24Address8
25Company9
26Name9
27Address9
28Company10
29Name10
30Address10
31Company11
32Name11
33Address11
Sheet3


Code:
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
 
Upvote 0
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]
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Last edited:
Upvote 0
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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top