Computer Tutoring Logo
Est. 2002
Menu Button

Excel Phone Numbers

The problem of the missing Zero?

If this were a mystery novel, the title of it would be: "The Problem of the Missing Zero". So what on earth do I mean? Well, to better understand the problem open up a blank Excel spreadsheet and type a phone number. Sort of like:

Excel phone number problem

Everything looks fine until you press enter then look what happens.

What's happened to the leading zero? It's disappeared! Why?

The problem lies with Excel understanding the entry has a number. Numbers cannot start with a zero, hence Excel removes the number. So, when you type a telephone number you want to see the number, so what do you do?

The answer is extremely simple. Before you type the phone number enter an apostrophe( ') before you type the phone number. When you press enter, Excel recognises the entry as text and allows you to keep the leading zero. Have a look at the following:

Excel Phone Number adding the apostrophe

Note that now you have added the apostrophe Excel has entered the number including the leading Zero. Also note that a green triangle is displayed giving you a potential warning of inconsistant data. The little hazard warning sign is also a give away click on the yellow hazard sign then select ignore error.

Excel - Number stored as text

Note that the green triangle and the error has disappeared.Now, if you look closely at the differenct between the phone number in the grid with the phone number in the formula bar. The phone number in the formula bar displays the apostrophe whereas the grid version doesn't. You can also accomplish this by entering a phone number with spaces such as the following:

Excel phone number with spaces

As I have typed the above phone number including spaces, Excel automatically recognises the number as a phone number and accepts it as such. But often when you receive phone numbers you want to enter them without spaces. Now you know how.

Should you want to know more about Excel and this was new to you, may we suggest you take our Beginners Excel Training course? We can come to a place convenient to you bringing laptops, manuals, projector and even some freebies. In short you will have everything you need to get the most out of this fantastic program.