How to create Multiple Mailing Labels Per Page in Word Without Using the Mail Merge Wizard.

Hi, Friends, Again a Simple Office Idea…to Work Small and Do It smartly.

I just wanted to create  mailing labels one for each recipient but multiple records on each A4 Size Paper so that I can cut them and paste to my envelopes since the Printer was not accepting large envelopes.

Just Like this…..

 

Here is my data sheet in Excel.

So my first option was to create labels in word mail merge.

I did open my data base in mail merge and started creating the Labels.

But to my surprise I can get only one Label per page ….or more labels on one page with same…data…i.e. one label at once or same label on full page.

I wanted to have 10 labels of different address on same page like this..

So finally after lots of google search…I have come to the point that Mail Merge will not work for this…and I have to do this manually… 

but if the records are 2-3 or 10 we can do it easily but if the records are like 400-500 than ????....it will take hours of time…doing this task…and when we are going to  courier the same…

But finally I found a simple idea to do this… and here is for all my friends and readers of Simple Office Ideas.

First of all go to your excel data base and use the function CONCATENATE this functions joins several text strings in one text strings..

Here is the formula that I used 

=CONCATENATE(B2,A2,",",C2,",",D2,",",E2,",",F2)

B2 = First Name in Address File.

A2= First Name in Address File.

C2= Address in Address File.

D2= City in Address File.

E2= State in Address File.

F2= ZIP in Address File.

“,” = For putting the , comma sign in between the address, and most important for our wok why you will know later so keep reading.

Now copy the cell where we have inserted the formula =concatenate

And Paste

Now we are having all the data in one coloumn..

Now Open a Blank word document and Save it where ever you want..

Copy all the data from coloumn H and paste to word, (CTRL+V)  it will look as below.

Now Select the Table on clicking the Table icon Now right click..and click on Table Properties.

And click on Borders & Shading 

I have taken border all and style double line and after that it was looking like this 

Now I wanted to have a format like

First Name Last Name

Address

City

Country

ZIP

So Now I have to break the data in above format and for that I used Find & Replace Function.

Just Select the Table and Click ALT+E+E (Word 2007) or CTRL+F and than click on Replace.

Enter  , comma in Find What Box and ^l  in Replace With Box and the result will be.

Your Data will become like this, but still we need some gap in between cells to cut them also columns should be at least two to have proper formatting..

Ok First to create gap in between cells right click on X icon of Table and click proprieties than options.

Now Tick the Box “Allow Spacing Between Cells” and choose how much distance you want..

I have chosen 0.1

And now my table was looking like..

But it was in single column only and rest of the page was waste…so I have made it two column through page layout option in word…which you can find in Top Most Raw of Word Tool bar

Now click on Page Layout and than Coloums and Choose Column 2 from drop down menu.

That’s all… your labels are ready…Just Print cut and stick to the envelopes.

You can use sticky papers from your local vendors or can online buy from Amazon…so that you don’t have to waste the time in glue… 


If you liked the Post Please share it for others benefit & dont forgot to share your comments.....