If you work with product numbers, zip codes, or any number that starts with a leading zero you will probably want Excel to keep the leading zero visible.
By default when you type the product number 012345 into a number formatted cell Excel will drop the leading zero and show 12345. To keep the leading zero you can use a custom number format.
1. Right click the cell to custom format and select Format Cells
2. In the Category box select Custom
3. In the Type box enter a zero for each digit in your number. In this case I entered 6 zeros because my product number was 6 digits long.
4. Hit OK
Also note: If you are using a Zip Code, Zip Code + 4, Phone Number, or Social Security Number that begins with a zero, Excel has built in number formats for these. You can access them by selecting Special instead of Custom in the Category box. Also make sure your Locale is set to English.
December 16th, 2004 at 2:50 pm
Thanks for the tip, I have dealt with 24hr time for quite sometime now without the leading zero. There is a ” time format for 24hr time but ” semicolons” are needed.
Thanks again
December 22nd, 2004 at 8:07 pm
No problem. I used to work with part numbers in spreadsheets quite often, keeping leading zeros was a must.
November 22nd, 2008 at 6:09 pm
So how do I merge tow cols both text one has leading zeros?
=A1&” “&D1 just doesn’t work it truncates the leading zeros even if the source and target are text fields