Dealing with Telephone numbers in Excel – Dialing in the correct format

Many have the impression that Excel is a tool to do maths. But the ones who are using it on daily basis know that Excel is way beyond just calculation and helps you maintain information and later extract it they way you like.

However, when it comes to number, there are situations where Excel stops behaving and starts acting like someone who knows nothing more than mathematics. One of such situation arises when you have to put in telephone numbers with country codes, area codes, service provider code etc.

Sorry! Wrong number…

Lets say you want to enter a telephone number which is: 0092421234567

Now if you type in this number in Excel and hit enter the result will be: 92421234567

And you will immediately understand what I was saying in the beginning.

Problem is that usually numbers start with a zero or couple of them. Now when you feed this number in worksheet, Excel will treat them as real numbers (instead of telephone numbers) and will get rid of the preceding zeros as in mathematical terms they are useless. For example 1 = 00001. Therefore, in order to make worksheet and calculations efficient and to reduce file size, Excel will get rid of, what it assumes, useless data. But it is in fact an important piece of information therefore, we need to have telephone numbers starting with zeros.

Following are some of the possible solutions and workarounds.

Method 1: Start numbers with apostrophe – Preceding apostrophe

This is probably the lamest solution you may find, but at times this works simply best than any other available. If you are keypunching the numbers then before typing in any number hit apostrophe key on the keyboard and then put the number. Now Excel won’t take away your precious zeros and the number will stay intact the way you typed.

Technicality behind this workaround is that once you include apostrophe then Excel is no longer taking your numbers as numbers rather it is considering them text and thus not changing it. One cool benefit of using apostrophe is that even if it is sitting there in the cell, you will not see it once you hit Enter key.
So remember that apostrophe can work as hidden override switch.

Method 2: Use + or – signs in your input

Suppose you have to type in 0092421234567 as a number, you can type in as: +92421234567. It is in correct format and recognized around the world and also Excel will not do any harm to it.

Another possible workaround is to use dashes in between e.g. 0092-42-123-4567.

Using + and – signs you are actually forcing Excel to treat these numbers as text and that is the reason Excel is not taking out zeros.

Using Excel’s Number formatting

The solutions we discussed above usually work only when you are punching in the data for the first time by hand. However, if you are importing the data from data base in Excel then above solution will do you no good and will take extra pains to employ them with no guarantee of being error free.

Therefore we come to even better solution i.e. Excel’s number formatting feature. Using this feature we have several options which you can use depending situation at hand.

Method 3: Convert cell formatting to TEXT

Above solutions where we employe apostrophe and + and – were actually helping us override Excel’s default feature to take numbers as real numbers to text. We can do this even without adding any type of signs only by changing the cell format settings.

Step 1: Select range of cells where you want to have telephone numbers and hit Ctrl+1 on the keyboard to invoke format cell dialogue box.
Step 2: From the options at left select Text and click OK.

Now even if you enter numbers in the cell, Excel will treat it as Text and won’t take away preceding zeros.

Method 4: Use built-in telephone number format

Just the way you changed cell formatting to text above, you can use built-in telephone number format to have formal looking results.
Step 1: Select the range of cells that you want to format with telephone number formatting and hit Ctrl+1 to bring format cell dialogue box up.
Step 2: From the options at left select Special and from the options at right select telephone number and click OK button.

Now when you type in your number the results will appear in proper format that you normally see on letter heads and other official documents.

However, there are some limitations and you may soon hit a road block with using built-in telephone number format. For example, my cell phone number with country code is: 00923224547934

Where;
0092 is country code
322 is service provider handler
4547934 is actual number

Now ideally this number should appear like: (92) 322-454-7934. But it appears like this: (92322) 454-7934. Well it may up to personal satisfaction and you may find it working but for me its not. I would like to have it formatted as earlier where I can easily distinguish what is country code and what is the rest. That brings me to the next power solution called custom number formatting.

Method 5: Use Custom Number Formatting

If Excel’s built in solutions are not giving you desired result than you can take controls in your hand and format the numbers the way you like. Accessing custom format option is no difficult, however harnessing this beast does require deep understanding of how this feature works.

Step 1: Select range of cells on which you want to apply custom number formatting and hit Ctrl+1.
Step 2: Once you have the dialogue box open select the last option from the list at left.

Once selected now you can type in the format of your choice and Excel will shape the numbers the way you want. Following are some examples that can help you get started and then what you can do with it is up to you 🙂

Desired result: I want 03224547934 but Excel will take out the first zero.
Solution: Including zero, my number is 11 digits long. Access custom format option and in the input field punch in 11 zeros. Although Excel will take out the first zero, but formatting this way will force Excel to show at least 11 digits and even if Excel shrinks the number back to 10 digits, to make up for 11 digit requirement Excel will put Zero back in again to abide by the formatting requirement.

On the same basis you can have 00923224547934 by having three additional zeros in the custom format type field.

Desired result: I want number to appear as: 0322-454-7934
Solution: In the cell have this number: 3224547934. In the custom format type input field have this code: 0000-000-0000

Desired result: +92-322-454-7934
Solution: In the cell have this number: 3224547934. In the custom format type input field have this code: +92-000-000-0000

Desired result: (92) 322-454-7934
Solution: In the cell have this number: 3224547934. In the custom format type input field have this code: (92) 000-000-0000

So here are some of the ways to fight telephone number problem. How are you fixing these issues and what are your creative ways to use custom number format? Let me know in the comment section below. Would love to read new ways of fixing this awful problem.

Don’t forget to checkout another post on similar issue:

• Adding specific Prefix to All cells or Specific cells in Excel – 3 Methods Explained