When entering telephone numbers, it’s always preferable to format the numbers into bite-sized chunks, either using dots, hyphens, spaces or parentheses. Most phone numbers can go anywhere from 9 to 12 digits.
Looking at a whole column of 12 digit numbers can make the reader’s eyes a little fuzzy, so it is always preferable to present these numbers in a more easy-to-read format, especially when the numbers involve international and national codes.
In addition to the issue of readability, there is the issue of auto-formatting. Let’s say you have a telephone number like ‘01873548923’.
After you type this number in a cell of an Excel sheet and press the return key, you will find the number auto-formatted to ‘1873548923’.
This is because Excel sees this as a regular number, not as a telephone number. It, therefore, assumes the leading zero as insignificant and removes it automatically.
In this Excel tutorial, we will show you how to format a range of cells containing telephone numbers in Excel so that you don’t have to manually format each cell with hyphens, spaces, or parentheses.
After that, we will show how you can make custom number formats so that your phone numbers follow the formatting conventions of your own country or any other country.
Formatting Phone numbers to the Built-in Excel Format
Suppose you have the dataset as shown below where column B has the phone numbers that need to be formatted.
Click here to download the example file and follow along
Below are the steps to format these phone numbers in Excel:
- Select the cell or range of cells that you need to format.
- From the Home tab, select the formatting dropdown in the ‘Number’ group. Then select ‘More Number Formats’. Alternatively, you can just right-click on your selection and select “Format Cells” from the popup menu that appears.
- This will open the ‘Format Cells’ dialog box. Select the ‘Number’ tab, and from the list of Categories, select the “Special” option.
- You will now see a list of formatting ‘Type’ options on the right side of the dialog box.
- Select the ‘Phone Number’ option from the list and click OK.
You will now find all your phone numbers formatted with parentheses, space and hyphens all in the right places, without you having to manually enter these.
Note that formatting your phone numbers in this way only changes how the numbers look on the excel sheet. It does not change the nature or value of the actual underlying number.
So, even though you have symbols like hyphens, spaces, and parentheses, your cells are still number cells, they haven’t converted to text.
This is also evident from the alignment of all the numbers towards the right, instead of the left. We know that numbers are always aligned to the right and text is always aligned to the left.
Also read: Show Negative Numbers in Parentheses/Brackets in Excel
Formatting Phone numbers to a Custom Format
Now the above method gives your numbers a general format for phone numbers.
However, if this built-in format for phone numbers does not meet your needs, you can go ahead and create your own custom format too!
Let’s say that instead of the format (XXX) XXX-XXXX, you want to display your numbers simply separated by dots, as in XXX.XXX.XXXX. Here’s what you need to do:
- Select the cell or range of cells that you need to format.
- From the Home tab, select the formatting dropdown in the ‘Number’ group. Then select ‘More Number Formats’. Alternatively, you can just right-click on your selection and select “Format Cells” from the popup menu that appears.
- This will open the ‘Format Cells’ dialog box. Select the ‘Number’ tab, and from the list of Categories, select the “Custom” option.
- You will now see a list of formatting ‘Type’ options on the right side of the dialog box. Select the built-in format that most resembles the one that you want to create. For example, 0.00. The format you select appears in the Type input box.
- Modify your selected number format in the Type box, so that you create the exact format that you need. In our case, we want dots after every three digits, so change it to: 000\.000\.0000
- Click OK.
This will create a new custom number format and after a click on OK, you will find all your phone numbers formatted to your requirement.
You might ask why we added a ‘\’ before each dot in our format.
The reason for this is that Excel sees dots as decimals, rather than just a symbol. So, if you put a dot (‘.’) without a backward slash (‘\’), Excel starts adding the decimal after all the phone number digits, which is not what we want.
The backward slash (‘\’) is called an escape character. It is used to escape the next character.
This means it is used to specify that the next character should not be considered as a decimal. Rather, it should be considered as a special character or symbol.
Note that the Custom number formats that you create in one workbook are not available in other workbooks. So you will have to create the format again if you want to use it in a new workbook.
Also read: How to Add Consecutive Numbers in Excel
International and Local Code format for US Numbers
Different countries have different conventions for writing telephone numbers.
There are international standards for phone number formats, like those issued by the International Telecommunication Union.
There are different national telephone numbering plans too and these vary from country to country.
According to the ITU E.123 standard, a US telephone number should be specified in the form +1 XXX XXX XXXX for international contexts and (0XXX) XXX XXXX for local contexts.
There is also a pseudo format that merges both as: +XX (XXX) XXX XXXX
Let us see how your custom number format would look for each of these forms:
- The general form: +00 (000) 0000000
- For national contexts: (\0000) 0000000
- For international contexts: +10000000000
+1 stands for the international access code for the United States.
As mentioned before, we added a slash (‘\’) before the zero for the national context form because we want Excel to see the first zero as a special character rather than a placeholder.
Also read: Convert Decimal To Fraction in Excel
International and Local Code Format for UK Numbers
For the United Kingdom, the number formatting is as follows:
- For metropolitan areas: (0XXX) XXX XXXX
- For non-metropolitan areas: (0XXXX) XXXXXX or (0XXX XX) XXXX
- For international contexts: +44 XXXX XXXXXX
+44 stands for the international access code for the United States.
Your custom format for each of the above forms would be:
- For metropolitan areas: (\0000) 0000000
- For non-metropolitan areas: (\00000) 000000 or (\000000) 0000
- For international contexts: +440000000000
Let us take a look at phone number formats for a few more countries:
International and Local Code Format for India
A landline number in India would have the format XXX-XXXXXXX, whereas a mobile number would have the format XXXXX-XXXXX. In international contexts, mobile numbers can be formatted as +91-XXXXX XXXXX.
+91 stands for the international access code for India. So your custom formats would be:
- For landline numbers: 000-0000000
- For mobile numbers (local context): 00000-00000
- For mobile numbers (international context):+91-0000000000
Phone number formats in other countries
Looking at the above formats, it is quite easy to see that no matter which country‘s phone number you are looking to follow, all you need to do is:
- Represent each phone number digit with a ‘0’.
- Position other symbols as they are in the original format.
- If the format requires the use of any special symbols like the dot symbol (‘.’), or a zero (‘0’), then represent it with a preceding escape character (‘\’).
That’s all. You can now represent your phone numbers in just about any format that you need using Custom Number Formats.
Conclusion
In this tutorial, we showed you how to format phone numbers to Excel’s built-in phone number format.
We also showed you how you can create your own custom phone number formats so that you can automatically make all your phone numbers follow any formatting convention for any country.
I hope you found this Excel tutorial useful!
Other Excel tutorials you may find useful:
