Format Phone Numbers In Excel (Table of Contents)
Format Phone Numbers in Excel
Formatting phone numbers in Excel is possible. It is a small but very innovative feature in Excel. Every country has a different country code and format of writing the phone numbers. Some have phone numbers separated by spaces, some allow brackets for country code, some have difference in number length and some put spaces after country code and rest of phone number follows the sequence.
Formatting phone numbers can be done by accessing Format Cells from Home menu under Format option’s drop down list.
How to Format Phone Numbers In Excel?
Formatting Phone numbers in Excel is very simple and easy to use. Let’s understand with different methods with some examples.
Format Phone Numbers In Excel – Example #1
Let’s consider some random phone numbers for formatting them. We have phone numbers which have a length of 10 characters, as shown below.
Now for formatting phone numbers in excel first select phone numbers then go to Home menu and click on Format as shown below. Once we click on Format, we will get a drop-down list, from where we will select Format Cells which is located at the bottom of the list as shown below screenshot.
Once we click on Format cells, we will get a dialog box which contains different functions related to formatting a cell like Number, Alignment, Font, Border, Fill and Protection.
4.9 (2,495 ratings)
View Course
As our task is to format the phone numbers, we will go in Number tab and select Custom option as circled in below screenshot.
As we can see and check by scrolling up and down Type box, that there is no predefined format of phone numbers available. So for customizing the phone numbers, delete the already filled numbers or text and enter your own format. As here, we deleted the word General from the type section and added “+91” as country code of India followed by “-” and then 10 zeroes which are the standard 10 digit numbers.
Once we do that, we will see that one of the selected cells with phone number, we will also get “+91-” added just above to the formatted structure as a sample. Then click on OK as shown below.
Once we click on OK, we will see the selected cells will get formatted as per the type which we have fixed in Format cell box as shown below screenshot.
Once we have formatted some test phone numbers, we can format any of the cell containing phone numbers with the same format.
Format Phone Numbers In Excel – Example #2
There is one more way of formatting phone numbers in Excel. Let’s consider the same sets of a sample phone number which we have seen in example-1. Now for formatting phone numbers, select the data first then do right on it. And select the Format Cells options as circled in below screenshot.
After that, we will get the Format Cells dialog box as shown below.
Now go to the Custom option down at the Number tab. Where we will find the type box, below it has the list of all customized number type, but not phone number format. For that, remove/delete the text or numbers present in type box, here we have deleted word General to enter the new customized phone number format.
Here, we will enter the new format for phone number. Here we have separated the country code with brackets and divided half of the numbers with “-“ in 3 parts and click on OK as shown below.
As we can see below, the format which we have set in Format Cells for phone numbers, it is now reflecting on the selected data.
Once our format is set, we can use it anywhere when we see phone numbers need to be formatted.
To check whether our newly created format of phone numbers is actually visible in format cells or not, do right anywhere on the sheet and select Format Cells options from the bottom of the menu. After that same window of Format cells will appear. Go to Number tab and select Custom option. Scroll down to look for our newly created format.
As we can see in below screenshot, we have circled the phone number formats which we have created in example-1 and example-2.
By this, we can create different types of phone number formats and use it as per our requirement.
Pros of Format Phone Numbers In Excel
For a large phone number data, it becomes very easy to understand the country code and actual phone number, if data is properly structured. We can categorize and segregate the different country phone numbers by their country code.
Things to Remember
- Always select the data before formatting phone numbers in Excel.
- Every country and region follow their own way of writing phone numbers. To sort out the numbers as per our required format, first understand the significance of the number system of a different country. For example, if you are formatting American phone numbers, their phone numbers start with +1 and follow with the proper 10 digits of phone number. Likewise, other countries also follow such kind of sequence.
- Mostly phone numbers of different country may have different numbers in total. Before applying any format to phone numbers first sort out the number with their length. With this, we can avoid having an incorrect format to those numbers on which it may not suit.
Recommended Articles
This has been a guide to Format Phone Numbers in Excel. Here we discussed methods and How to Format Phone Numbers in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
