Using Mail Merge on 80 labels per sheet, 1-3/4 X 1/2 labels to Create Consecutively Numbered Labels

The 80 labels per sheet ML-8100 is normally used as a return address label. However, some customers use them simply as a numbering or coding label. We’ve used the 80 labels per sheet ML-8100 template as the primary document and created an Excel file as data file in our mail merge. Mail merges are not used exclusively for mailings. They’re used to insert all kinds of data into various types of documents. If you have a need to print cells with consecutively numbers, using Excel together with Word works great.

The first step is to create the Excel file with consecutive numbers. Lets say for instance you want to number your labels from 1 to 2000. We’ll also want to pad the numbers on the left with zeros. It just looks neater that way. First name the column A by typing the word NUMBER into cell A1. Select the entire first column by clicking on the column heading A. Once it’s selected, right click on the column, and chose Format Cells from the dialog menu. Then choose the Number tab, and click on the Category ‘Custom’. In the Type field, we just type 6 zeros, 000000. Now when you type the number 1 in cell A2, it’ll show as 000001.
zero fill excel cell
To fill in the column, hold a right click on the lower right hand corner of the cell you just typed in. As you hold it, also press the Ctrl key, and drag straight down to the bottom of the page. Holding the Ctrl key will increment the values as you drag the mouse cursor down to however far you want to go. I went to 2000, and I renamed the file to 1-2000.xlsx

zero fill excel cell

I’ve got to admit something here, we really didn’t need to format the NUMBER column, because if we use that as a merge field, Microsoft Word only sees what the original input was, eg. 1, 2, 3 …, and not the formatted version we see 000001, 000002, 000003 …. I forgot about that, but we’ll leave it here, just to show you how to format a column.
To create the left zero padded copy of the NUMBER cell, we make another column that copies the first using the TEXT() function. So in the cell B2, type =TEXT(A2,”000000″). Then right click on the lower right corner of the cell, this time, NOT holding the Ctrl key, just right click, hold and drag the cell to the bottom of the spreadsheet.
zero fill excel cell

So now that we have our data, we just need the Primary Merge Document, shown below.

zero fill excel cell

If you’re quick at copy and pasting using the keyboard and mouse combination, creating the above document should be pretty quick. Caution: in the last cell of the document in the lower right of the document, leave off the merge command «Next Record», because Word automatically advances the record to the next record at the end of the page. If you put the «Next Record» in the last cell, you’ll wind up skipping a record, and the next page will start at 82 instead of the correct 81.
And the merged result is below.

zero fill excel cell

2 thoughts on “Using Mail Merge on 80 labels per sheet, 1-3/4 X 1/2 labels to Create Consecutively Numbered Labels

  1. I am in Martin, TN. Where in Jackson, TN, or Paducah, KY, can I buy a box of OS-281 Address Labels. I have no need for a case.

Leave a Reply

Your email address will not be published. Required fields are marked *