Software

Entering leading zeroes in Excel

By default, Excel won't display or store leading zeros. Here are three handy ways to get around the problem.
Leading zeros are those pesky place-holding 0 digits at the beginning of a number. Unfortunately, when you enter such a value, Excel applies the General numeric format and tosses out the leading zero. Excel won't display or store the leading 0 digit (see the Formula bar).

When these values are text -- part numbers, ZIP codes, and so on -- you don't have a problem. Simply precede the value with an apostrophe character ('). You can tell the value is text because it's left-aligned and you can see the apostrophe in the Formula bar. Sometimes, these entries are true numeric values and you will need to evaluate them in mathematical processes. When this is the case, you can force Excel to display placeholder 0 digits by applying a custom format as follows:

  1. Select the cell or range in question.
  2. Right-click the selected cell or range and choose Format Cells from the context menu.
  3. Click the Number tab.
  4. Choose Custom from the Category list.
  5. In the Type field, enter the number of 0s necessary to accommodate the largest value. For instance, if the largest value contains four digits, enter four 0s.
  6. Click OK and Excel displays leading values.

Your users can enter the leading zero(s), or not -- Excel won't care. However, this particular format doesn't work with decimal values. If you enter the value .7, the format rounds the value to 1. To accommodate decimal values, enter placeholders for the appropriate number of digits. For instance, if your want Excel to display four digits to the left and two digits to the right of the decimal, enter the custom format 0000.00.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

11 comments
drex.pincock
drex.pincock

This has really helped me, but we have part numbers from two different compnaies in our ERP system. I can get one or the other to display properly but not both in one column. 011722 022670 024256 050220 001.00244 001.00298 001.00311 075.00021 Please help !!!!!!!!!!!!!

jngant
jngant

If you receive an Excel file with, for instance, ZIP codes entered as numbers, you can indeed get the screen to show (and print if needed) leading zeroes in the eastern states' ZIPs using the "Format" for the ZIP column (the "00000" Custom approach). If you export, however, say to a FoxPro or tab delimited file, you may not get the leading zeroes. It seems to depend on the version of Excel. As I recall, prepending an "'" works, but can be tedious unless you are adept with macroes and formulae in Excel and can automate the process.

kahlmari
kahlmari

None of the links work and when I search the downloads area for Excel data formatting, I get nothing related, too bad it was a cool idea.

TheProfessorDan
TheProfessorDan

I do find the solution in this article is cool but I am wondering why you would just change the format of the cell to text. It allows you to add the leading zero and you can still use it for mathematical equations, at least for the functions that I tested.

Deadly Ernest
Deadly Ernest

memory, but have you tried Solution A. setting the custom setting to include a decimal range of five numerals, and set the display to not show decimals if a whole number Solution B since these are part numbers and not the normally processed numerals such as dollar amounts or distances where you want to add them up and do mathematical functions on them, why not just set the field as a text field and enter the part numbers as a text string (the same as you would for a name). When you do a search you still search to match the string so it should work. Then depending upon how you want it to display you can set the fields to be left aligned or center aligned - this could work in both solutions above - and the type of part number would stand out simply by how big it is in the column.

wizard57m-cnet
wizard57m-cnet

Try re-posting in the Q&A section.The 'Discussion' forum is for matters of general discussion, not specific problems in search of a solution. The 'Water Cooler' is for non-technical discussions. You can submit a question to 'Q&A' here: http://www.techrepublic.com/forum/questions/post?tag=mantle_skin;content There are TR members who specifically seek out problems in need of a solution. Although there is some overlap between the forums, you'll find more of those members in 'Q&A' than in 'Discussions' or 'Water Cooler'. Be sure to use the voting buttons to provide your feedback. Voting a '+' does not necessarily mean that a given response contained the complete solution to your problem, but that it served to guide you toward it. This is intended to serve as an aid to those who may in the future have a problem similar to yours. If they have a ready source of reference available, perhaps won't need to repeat questions previously asked and answered. If a post did contain the solution to your problem, you can also close the question by marking the helpful post as "The Answer". . .

Excelmann
Excelmann

I even used the address from the properties and could not get the downloadable file.

sdmayhew
sdmayhew

when you are preparing files to be imported into Oracle dB like Remedy it needs to be custom with 15 0's Also Custom does not hold the formatting setting - I have to redo it every time

ssharkins
ssharkins

Check it in SUM() or AVERAGE() and tell me if it works for you.

johnnymegabyte
johnnymegabyte

Just set the column's Properties 1. Highlight column 2. Right Click 3. Choose Category = Custom 4. type in 00000 where it says General 5. click OK I do this all the time for UPC's and other numbers that have to show the leading zeroes

Editor's Picks