One of the problems beginners often struggle with is how to enter phone numbers into a list of contacts in Excel.
If you type a number with a leading zero and no spaces (e.g. 01234567890) - the leading zero disappears. If you include spaces (01234 567890) it stays. But if that space is merely the first character (or the last), the leading zero disappears again.
If you type a number with a leading plus sign AND spaces (e.g +44 1234 567890) it comes up with an error message "We have found a typo in your formula" and regardless of whether you press Yes or No to accept or reject their suggested correction, things don't work out the way you want. So what's going on?
---
(images/blog/Phone_400x267.jpg)
Well, the first thing to understand is that Excel is trying its best to figure out what it is you are trying to type in - a number, a formula or a piece of text, because it stores and interprets them differently. When you type in a phone number - it gets confused.
The second thing to understand is that a phone number isn't really a number - it's a piece of text. I agree it might only contain numeric characters, but you won't ever add or subtract from it, it isn't valid to include a decimal point, and it isn't correct to drop that leading zero, as it's not the same phone number afterwards. So trust me, it's a piece of text.
When you type a string of digits, Excel's default behaviour is to assume you mean to type in a number - so that's why that leading zero is removed. What Excel thinks you've entered is the number 1,234,567,890 (ie a billion or so) and it's dropped the leading zero because most of the time it's standard practice to display numbers without leading zeros. So why DOES it work when you include a space? Well that's because Excel knows that numbers don't contain spaces, so when you include one, you clearly intend Excel to process this as a string of numeric characters - i.e. text. However, that's not true if the only spaces are at the beginning or end, as it trims those off when trying to read a number.
Starting the phone number with a "+" sign and including spaces makes Excel assume that you are trying to type in a mathematical formula, and when it can't make sense of it all - it assumes _you've_ got things wrong, and then makes a ham-fisted job of a suggested correction by stripping out spaces and assuming it's a number again.
So here are several suggestions about how to avoid the problem. Pick one you like.
1) Always include spaces somewhere in the string of numbers. This is the easiest approach.
2) Format the number using an international prefix. Here, the existence of the round brackets is enough to tell Excel that it's text rather than a number. Adding spaces improves the formatting further of course.
2) Always start the entry with a single quote character - this _forces_ Excel to accept the entry as text, but it will give you an annoying 'green triangle' warning that a number is formatted as text, unless there are some embedded spaces.
3) But probably the best idea (if you are entering lots of phone numbers into a table) is to _pre-format the whole column_ as Text format.
To do this..
a) Select the whole column (say column D)
b) On the Home tab, find the Number Format picklist (here displaying General) and pull down to select "Text". If you can't see it on the list - scroll down further.
Now - no matter what you type into the column, it won't try to convert it to a number, or interpret it as a formula. You will still get a green triangle warning, however if it looks exactly like a number, but at least you won't lose the leading zero.
