How to Easily Enter Phone Numbers in Excel Cells!

If I have to name the question I’m most frequently asked, this will probably among the top 3: How do I enter a phone number in Excel? The problem: When typing phone numbers, Excel recognizes them as numbers and removes leading zeros.

Summary

The fastest way is to insert ‘ (single quotation mark) when you type a phone number. So, instead of

001123456789 

you type

'001123456789

or you type

001 12 34 56 789

The problem with phone numbers

When you enter a phone number in Excel, you usually start with the area or country code. These codes commonly start with a 0 (zero) or a plus sign. Unfortunately, Excel still recognizes these as numbers so that you can calculate with them etc. That’s why Excel removes leading zeros or plus characters.

Here is what happens:

The detailed solutions for typing phone numbers in Excel

Solution 1: Insert a single quotation mark

This is the safest and often the fastest way: Start typing the phone number with a single quotation mark. That way, Excel is forced to recognize your number as a text value.

Solution 2: Insert blanks to separate the phone number

Solution 3: Set formatting to text

The third option is to set the cell format to text within the cell formatting options. The fastest way would be to go to the Home ribbon and select “Text” in the number format drop-down as shown below:

When you now type a phone number, it should be recognized as text automatically and therefore the leading 0 or + remain.

Unfortunately, this doesn’t work 100% reliable. In some cases Excel might switch back to the number format and remove all leading 0 or + characters.

Solution 4: Create a custom number format for phone numbers

All previous methods have in some way set the cell format or contents to text cells. The “downside” of this is that you cannot really continue calculations with a phone number. Also, they might look messy if inserted in different styles (for example, having spaces in-between, +-signs vs. leading 0).

US Phone Numbers

Excel offers a number format “Phone Number”. Unfortunately, the built-in options are quite limited.

Select your cell and open the Format Cell and click on “Special” on the Number tab. Select Phone Number as shown below (please note: “Locale English (United States)” is selected; these built-in codes aren’t available for all locales).

All other phone numbers

You just want to display one or two leading zeros? This can quickly be done with a custom number format.

Select your cell and open the Format Cell and click on “Custom” on the Number tab. Insert the following code in the “Type” field:

"00"0

If you want to insert a plus sign instead of two leading zeros, use this code:

"+"0

For more information about custom number format, please refer to this article.

Expert tip: Use Professor Excel Tools to force cells to text

If you want to bulk insert the single quotation marks to many cells, you can use our Excel add-in Professor Excel Tools. Just select all your cells, go to the Professor Excel ribbon and click on “Force to Text”.

Image by Mabel Amber, who will one day from Pixabay