Controlling Address Label Field Length in Excel

When using an Excel file to complete a mail merge keep your field lengths from getting out of hand, even if you’re using a 4″ wide address label like the ML-1000, ML-1400, ML-2000. If you’re using a database such as Microsoft Access, OpenOffice Base, LibreOffice Base, dBase, FoxPro, MySql, it’s pretty straight forward to control field lengths by just setting the field length to a fixed value. If you’re using Excel, it’s not as straight forward as that.

Although Microsoft Excel is not a database program, it does provide some functionality to control the length of your input fields. The Document shown, which you can download, named address-label-field-length.xlsx an Excel 10 spreadsheet contains a worksheet which has typical fields that would be used for a mail merge.

To prevent field lengths that are too long from being entered, you can place a limit on the field lengths for anyone who is entering the data.

Click on the Data tab shown highlighted in yellow.

Now select the entire column by clicking on the column marker. Here we selected FirstName, which has the letter A as the column marker.

Click on the Data tab. then select the Icon labeled Data Validation.

Click on the upper half of this icon that is circled in red.

When the Data Validation window pops up, choose the Setting tab. From the drop down Menu titled Allow, choose Text Length. Then move down to the next drop down menu titled Data. Choose less than or equal to.

Then move down to the next drop down menu titled Maximum. Here we input a maximum of of 20 characters for the First name field.

Now move to the Input Message tab. If you like, you may enter a message that will appear when you are entering data. Here we chose to enter a title and message that would remind the user of the maximum field length.

In the Title field, we put Firstname field Length , for the Input message field, we entered the words maximum of 20 characters.

Next we clicked on the Error Alert tab. Since we do not want the user to enter a first name with more than twenty characters, in the Title field we entered the word Error, and in the Error message field, we entered the words Maximum field length exceeded.

So now when a user enters a first name with more than 20 characters, an error dialog box will popup.

Note that now when a user clicks inside any first name field, a floating message box will appear with the data we input into the Input Message tab in the Data validation window. Also note that now, when a user enters data that is greater than 20 characters in the first name field, the error dialoge box containing the data that we entered into the error alert tab in the data validation window.