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
'001123456789or you type
001 12 34 56 789The 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"0If 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”.
This function is included in our Excel Add-In ‘Professor Excel Tools’
(No sign-up, download starts directly)
More than 35,000 users can’t be wrong.
Image by Mabel Amber, who will one day from Pixabay
