Did you know that when you enter a telephone number into Excel, you can get Excel to display the number starting with a zero. Several people commented on a post I made that showed the proper way to do this with an incorrect solution. If you do this, expect problems later.
The Problem
Excel sees cell numbers as numbers, so telephone numbers that start with a zero lose that zero.
Incorrect: Column or cell as Text
If you set the column or cell to Text, either by using an apostrophe when entering the number, or by highlighting the column and pressing Ctrl-1 and setting the column to Text doesn’t solve the problem. You will have to edit each telephone number you have added.
If you have to import these numbers from another program, having to edit thousands of telephone numbers is daunting.
Solution: Column as Custom
Instead, highlight the column and choose Custom then in Type enter eleven zeroes. This will force any number entered in this column to be padded with a zero if it is 10 digits long.
There is a slight downside, Microsoft doesn’t have a UK telephone code set in it’s special number format, so it will replace it with the Polish version called Numer PESEL, which causes no problems, but you can’t change the locale to English – United Kingdom for this column or it resets to General if you try.
Conclusion
It is easy to fall into bad habits with Excel, like using Text instead of numbers to display something correctly. However, if you made a number a text in one cell then tried to add up the cells, you will get entirely the wrong amount which spells trouble later.
