Excel – Retaining a leading 0 (zero) when entering Post Codes – great for mail merging

PROBLEM: retaining a leading 0 (zero) when entering Post Codes. When you enter a post code that has a leading 0 (zero), e.g. 0642, you see only the last 4 digits, not the leading zero.  This is especially frustrating when using an Excel file in a Mail Merge!

SOLUTION: learn how make the 0 stay!

In my post “Excel – Why does the 0 disappear?‘ I explain how you can use Custom Formatting to control figures when leading zeros go missing.

In this post I want to share with you how to do the same when dealing with post codes.

Method 1: Formatting post codes for use in Excel

Let’s start by looking at formatting 4 digit post codes that begin with a zero, e.g. 0642.  You may have already discovered that even if you type the full post code Excel drops the zero as soon as you press ENTER.

We’ll change the format of the cell to ensure the zero is held in place.  This option is excellent if you will be sorting or filtering by post code.  The post code remains a number and therefore sorts and filters beautifully.

Step 1. First select the cell or range of cells containing the post codes.

Step 2. On the Home tab click the drop-down arrow on the Number Format box and then click More Number Formats (or you can click the dialog box launcher on the Number group).

Step 3. On the Number tab select Custom from the Category list.

Step 4. In the Type box type 0000.  This formats the cell(s) so that 4 digits are always displayed.  Therefore if only 3 numbers are entered a leading zero is always placed in front.

Step 5. Click OK. All 3 digit post code numbers will now be displayed with leading zeros.

Method 2: Merging post codes into Microsoft Word

Method 1 is great if you are leaving the data in Excel and it wont be used as part of a mail merge in Word.  Sadly, if we tried to use method 1 to format post codes for merging we would find that the leading zero, once again, does a disappearing act and we are back where we started.  This is because the post code is still actually a number.  The formatting we applied is what is placing the zero before it.  Only the number, not the format, is pulled through into Word.

To prepare the post codes for mail merge we need to create another column and then turn the post code into text.

Step 1. Insert a column next to the column containing the post code.

Tip: leaving the original column is a good idea as the data in this column can still be used for sorting and filtering.

Step 2. We’ll now use the TEXT function to turn the post code into text.  For the example below the function will read =TEXT(F2,”0000″).  This takes the post code in F2 and applies a 0000 format to the post code.  Therefore when it finds a post code with only 3 digits it places a zero before it.

Step 3. Copy or repeat the same for all post codes that need to be formatted as text.

Step 4. Save the file.

Step 5. Now go to Word and start your mail merge.  When you select your recipients be sure to select the Excel file.

Step 6. When you place the Merge fields into your Word document make sure you select the post code text field, not the post code number field.

Step 7. All post codes will now be shown correctly.

Note: if you have more than one column of data that contains formatted numbers like percentages and currency values you may like to take a look at the Microsoft Office Support page Prepare your Excel data source for a Word mail merge. 

For more fantastic tips on how to retain leading zeros in Excel check out our blog ‘How to add zero in front of a number in Excel’.

Was this blog helpful? Let me know in the comments below.