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
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:
Figure 1. The Number tab of the Format Cells dialog box.
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:
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.
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!
If you use an Excel worksheet for entering data (a quite common task, actually), then you need to be concerned with how ...
Discover MoreYou 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 MoreWant to figure out if the text in a cell is bold? The best approach is to use your own user-defined function, as ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments