Leaving Leading Zeros in Place

Written by Allen Wyatt (last updated October 26, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


4

Gary regularly sends out a mailer to his company's subscribers, so he keeps track of their addresses in an Excel worksheet. Many of those subscribers have ZIP Codes that begin with a zero. When he enters them into a cell, Excel strips the leading zeros. He wonders how to stop them from being stripped.

There are two general ways you can handle this situation. These general ways correspond to the two basic, overarching ways that information can be stored in a worksheet—either as text or as numbers.

Let's say, for instance, that you Gary wants the ZIP Codes stored as numbers. In this case, all you need to do is to make sure that the cells containing ZIP Codes are formatted for the leading zeros. The easiest way to do this is to follow these steps:

  1. Select the cells that contain ZIP Codes.
  2. Press Ctrl+Shift+F. Excel displays the Format Cells dialog box.
  3. Make sure the Number tab is displayed.
  4. In the Category list, choose Special. (See Figure 1.)
  5. Figure 1. The Number tab of the Format Cells dialog box.

  6. In the Type box, choose ZIP Code.
  7. Click on OK.

If, in your version of Excel, you don't see the ZIP Code choice in the Type box (step 5), then you should choose Custom in the Category list and create a custom format that consists of five zeros (00000).

Understand that if you choose this approach to formatting the ZIP Code cells, Excel will still remove the leading zeros, but the display will show them because you've formatted the cells that way. (You can see this by selecting a cell containing a ZIP Code with leading zeros and then looking in the Formula bar. The value there will not have the leading zeros.)

This means that if you are using mail merge in Word, pulling the addresses from your Excel worksheet, that the leading zeros won't show up in the merged data, either. If that is your intended use of the addresses, then you should consider using the other approach to storing ZIP Codes, discussed next.

The other general method is to store the ZIP Codes as text. This is very appropriate for ZIP Codes because the chances of you doing some sort of math with them is slim, at best. There are three ways you can treat them as text. First, when you enter the ZIP Code, you could precede it with an apostrophe. Thus, you would enter '00123 instead of 00123. The apostrophe tells Excel to parse the entry as text.

The second method is to format the entire column as text, before you start entering the ZIP Codes. In this approach you don't need to precede the ZIP Code with an apostrophe as you've already informed Excel that everything in the column should be treated as text.

The third method is, perhaps, the most interesting—you can configure Excel to treat anything containing leading zeros as text, automatically. This approach is available in Excel 2019, 2021, and Excel in Microsoft 365:

  1. Display the Excel Options dialog box. (Display the File tab of the ribbon and then click Options.)
  2. At the left of the dialog box click Data. (See Figure 2.)
  3. Figure 2. The Data options in the Excel Options dialog box.

    Clear the checkbox next to the Remove Leading Zeros and Convert to a Number option.

    Click on OK.

    Now, whenever you enter something starting with a zero, Excel automatically formats the cell as text. Technically, this is not correct—the cell is not formatted as text automatically. Instead, the entry is preceded by an apostrophe automatically. You can see this behavior if you follow the above steps, enter a number that starts with a zero, and then select the cell. The format of the cell remains what it was before, but the Formula bar clearly shows that the number is preceded by an apostrophe, even though you didn't enter one.

    There is something to keep in mind if you preceded individual cells with an apostrophe, including the change-the-parsing approach just described. It is very possible to end up with both numbers and text in your ZIP Code column. This can affect how your data is sorted. Excel recognizes this and will give you the option, when sorting, to treat anything that looks like a number as a number during the sort. You should, obviously, take advantage of this option.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11274) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Displaying Toolbars

Toolbars allow you to access common tasks quickly and easily. Excel provides a wide variety of toolbars, and you can even ...

Discover More

Searching for Formatting

When searching for text, Word can pay attention to more than just the characters in the text. It can also pay attention ...

Discover More

Displaying Edits by Date

Track Changes is a great tool; it allows you to see what changes were made in a document and then determine whether you ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Ensuring Conditional Formatting and Data Validation is Copied

If you use an Excel worksheet for entering data (a quite common task, actually), then you need to be concerned with how ...

Discover More

Retaining Formatting After a Paste Multiply

You can use the Paste Special feature in Excel to multiply the values in a range of cells. If you don't want Excel to ...

Discover More

Determining If a Cell is Bold

Want to figure out if the text in a cell is bold? The best approach is to use your own user-defined function, as ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is four less than 6?

2024-10-26 07:02:26

Alex Blakenburg

@Mike J - Thanks for responding. Microsoft is making it rather challenging to get a handle on which versions have what functions, so appreciate your insights.


2024-10-26 06:52:19

Mike J

@Alex

I responded too soon. Although the Options/Data/Automatic_Data_Conversions are available to select, none of them appear to 'stick' - they are not selected when Excel is re-started, and this one doesn't appear to work! So, No, it does not seem to be available in 2021.


2024-10-26 06:51:03

Mike J

@Alex

In Office 2021, this option is available but, under the heading 'Automatic Data Conversion',
'Convert continuous letters and numbers to a date' is not.


2024-10-26 05:20:11

Alex Blakenburg

I would be interested in anyone using Office 2019 or 2021 confirming that the third method is available to them ie "Automatic Data Conversion". My understanding is that it was first released to MS365 Current Channel in Version 2309 which was release 28 Sept 2023 so I would not expect that 2019 or 2021 would have that update.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.