Leaving Leading Zeros in Place

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



Figure 1. The Number tab of the Format Cells dialog box.

5. In the Type box, choose ZIP Code.
6. 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.)



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.