February 11

0 comments

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

By Sharyn Baines

February 11, 2015

Mail Merge

Sharyn Baines

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.

  1. 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.

  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.

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.

Sharyn is an expert trainer.  She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand.

She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.

If you enjoyed this you may also enjoy...

HOW TO delete a blank or extra page in Word document (5 quick fixes)

[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. 

​Read More

Word – merging a list of names and addresses to labels

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

​Read More

Don’t be fooled – protecting isn’t bullet-proof

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

​Read More

Word – How to change measurements from inches to centimetres

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

​Read More

Sharyn Baines

About the author

Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand. She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"0328f":{"name":"Main Accent","parent":-1},"7f7c0":{"name":"Accent Darker","parent":"0328f","lock":{"saturation":1,"lightness":1}}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"0328f":{"val":"var(--tcb-skin-color-0)"},"7f7c0":{"val":"rgb(4, 20, 37)","hsl_parent_dependency":{"h":210,"l":0.08,"s":0.81}}},"gradients":[]},"original":{"colors":{"0328f":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45,"a":1}},"7f7c0":{"val":"rgb(4, 21, 39)","hsl_parent_dependency":{"h":210,"s":0.81,"l":0.08,"a":1}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"0328f":{"name":"Main Accent","parent":-1},"7f7c0":{"name":"Accent Darker","parent":"0328f","lock":{"saturation":1,"lightness":1}}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"0328f":{"val":"var(--tcb-skin-color-0)"},"7f7c0":{"val":"rgb(4, 20, 37)","hsl_parent_dependency":{"h":210,"l":0.08,"s":0.81}}},"gradients":[]},"original":{"colors":{"0328f":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45,"a":1}},"7f7c0":{"val":"rgb(4, 21, 39)","hsl_parent_dependency":{"h":210,"s":0.81,"l":0.08,"a":1}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article

JOIN FREE! 'Excel at Work Insiders Group' for free tutorials, mini-courses, videos and blogs!

>