Tom’s Tutorials For Excel: Enter Zip Codes and Social Security Numbers With Leading Zeros

Tom’s Tutorials For Excel: Enter Zip Codes and Social Security Numbers With Leading Zeros

When you enter numbers that start with a leading zero such as Social Security Numbers or United States zip codes, you must stop Excel from automatically hiding the leading zero, so your full number including the leading zero is displayed. There are a few ways to accomplish this:

• Enter a preceding apostrophe ‘ character followed by the actual number (example ‘01234).
• Format the cells as Text before doing the data entry (Alt+O+E > Number tab > Text > OK).
• Format the cells, example Zip Codes as Alt+O+E > Number tab > Custom > 00000 (5 zeros) > OK.
• Format the cells for any case with Alt+O+E > Number tab > Custom > “0”# (with the quotes) > OK.

Note that formatting only affects how you see what you entered, not necessarily what you actually entered. The underlying actual value in the cell is unchanged regardless of whatever formatting is applied to the cell. For example, with a custom format “0”#, entering 02567 or just 2567 will visually appear in the cell as 02567, but in the formula bar will be its true actual value 2567.