Updated August 21, 2023
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 for 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 different methods with some examples.
Example #1
Let’s consider some random phone numbers for formatting them. We have phone numbers of 10 characters, as shown below.
Now for formatting phone numbers in Excel, select phone numbers, 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 which we will select Format Cells, 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 Number, Alignment, Font, Border, Fill, and Protection.
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, no predefined phone number format is available. So for customizing the phone numbers, delete the already filled numbers or text and enter your own format. 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, 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 OK, we will see the selected cells will get formatted according to the type we have fixed in the Format cell box, as shown in the screenshot below.
Once we have formatted some test phone numbers, we can format any 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 sample phone numbers that 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, which lists all customized number types but not phone number format; for that, remove/delete the text or numbers 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, divided half of the numbers with “-“in 3 parts, and click on OK, as shown below.
As we can see below, the format we have set in Format Cells for phone numbers now reflects the selected data.
Once our format is set, we can use it anywhere when phone numbers need formatted.
To check whether our newly created format of phone numbers is visible in format cells, 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 screenshot below, we have circled the phone number formats we created in example-1 and example-2.
We can create different phone number formats and use them per our requirements.
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 the 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.
- First, to sort out the numbers as per our required format, understand the significance of the number system of a different country. Every country and region follows its own way of writing phone numbers. 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 different countries may have different numbers in total. Before applying any format to phone numbers, sort them by length. With this, we can avoid having an incorrect format for those numbers that may not suit.
Recommended Articles
This has been a guide to Format Phone Numbers in Excel. Here we discussed methods, Format Phone Numbers in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
