FREE CHEAT SHEET
All the key points in one handy PDF
Plus, collect a FREE copy of our 30 Excel Power Tips.
ENTER YOUR DETAILS FOR IMMEDIATE ACCESS
Have you ever typed a number with one or more leading zeros, like 00123 and watched as Excel drops the leading zeros as soon as you press Enter.
Frustrating, isn’t it!
So how do you fix it?
There are 2 methods. One is better than the other for a quick set up and easy maintenance. There is also a third method which combines elements from each of the first two.
Now, when you enter your data with leading zeros, the leading zeros remain and do not disappear when you press Enter.
A couple of things to watch out for:
The new custom format will now be applied.
Phone numbers will now have the punctuation and spacing. For example, enter 0123456789 into a custom-formatted phone number cell (see above) and press Enter. It will automatically reformat to (0123) 456–789.
Numbers that previously dropped the leading zeros will now display the leading zeros. For example, enter 123 into a custom-formatted number cell (see above) and press Enter. The leading zero will be added automatically to display 00123.
If you import data into Excel that was exported from another program or website, the data is often formatted entirely as text.
You can click the smart icon (see above), then choose Convert To Number but you still don’t have the leading zeroes!
An easy way to convert a lot of ‘text’ numbers to ‘numeric’ numbers (if that makes sense) and add leading zeroes is to follow this process (assume the ‘text’ numbers are in column A):
Numbers start in text format[/caption]
I hope that gets you out of a fix. Add a comment below if this helped you.
Please share your thoughts and feedback in the comments below, and if you found this post useful, please share a link on your social networks.
Cheers. Here's to your learning and success. Enjoy the rest of your day.
Please log in again. The login page will open in a new window. After logging in you can close it and return to this page.