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.
- First select the cell or range of cells containing the post codes.
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).
3. On the Number tab select Custom from the Category list.
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.
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.
- 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.
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.
3. Copy or repeat the same for all post codes that need to be formatted as text.
4. Save the file.
5. Now go to Word and start your mail merge. When you select your recipients be sure to select the Excel file.
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.
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. Once you are on the page scroll down to the notes on Dynamic Data Exchange (DDE) for a Mail Merge. This is a fabulous way to retain formatting on large data lists.
If you enjoyed this you may also enjoy...
[Watch on YouTube] / [Subscribe to our YouTube Channel]Needing a hand to remove a blank or extra page in Microsoft Word? If you’re reading this, you’re probably wrestling with an extra page break or a blank page or a lot of space within your document somewhere. I’m going to show you several different fixes starting with the easiest and moving to the more unusual fixes.
If you would like your audience handouts to have a professional look and conform to your organisation’s brand, consider importing your presentation into Word where you can then including additional text like a cover page, your contact details, and your company logo. Audience hand-outs can easily be printed directly from PowerPoint simply by navigating to
Merging a list of names and addresses to labels from your customer database can save you (and your hand) a lot of pain. Sending Christmas cards to your valued customers is a lovely gesture, but hand-writing the envelopes is a killer. Creating a mail merge to labels from your customer database can save you (and
When I’m asked to teach a class on how to restrict editing within a document, the first 5 minutes of my instruction is usually spent explaining that protecting isn’t bullet-proof. Why do I do this? Because I have met so many people who assume that restricting the editing of a document by adding a password is
If you are familiar with working in centimetres, not inches, it can be a tad frustrating if Word is displaying measurement settings in inches. For example, if I wanted to set my Hanging Indents to 1.25cm I know for a fact I would be lost if my measurement settings were only being displayed in inches. I
PROBLEM: wasting time creating tables. Especially when trying to create complex tables where columns don’t align and content needs to be reorganized. SOLUTION: Save yourself HUGE amounts of time…learn the shortcuts!