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 phone numbers. Some have phone numbers separated by spaces, some allow brackets for country code, some have a difference in number length, and some put spaces after country code and the rest of the phone number follows the sequence.
Formatting phone numbers can be done by accessing Format Cells from the Home menu under the 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.
Example #1
Let’s consider some random phone numbers for formatting them. We have phone numbers that have a length of 10 characters, as shown below.
Now for formatting phone numbers in excel, first select phone numbers, then go to the 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 that contains different functions related to formatting a cell-like Number, Alignment, Font, Border, Fill, and Protection.
4.9 (8,760 ratings)
View Course
As our task is to format the phone numbers, we will go into the Number tab and select the Custom option as circled in the below screenshot.
As we can see and check by scrolling up and down the Type box, there is no predefined phone number format 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 the 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 a phone number 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 according to the type we have fixed in the 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 in the same format.
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 the 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 the type box, here we have deleted the word General to enter the new customized phone number format.
Here, we will enter the new format for the 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 the Number tab and select the Custom option. Scroll down to look for our newly created format.
As we can see in the 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 them as per our requirement.
Pros of Format Phone Numbers In Excel
It becomes very easy for a large phone number data 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 a phone number. Likewise, other countries also follow such kind of sequence.
- Most phone numbers of the different countries 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 Format Phone Numbers in Excel and practical examples, and a downloadable excel template. You can also go through our other suggested articles –
