Formatting Phone Numbers in ExcelEasy Guide
Have you ever wondered why people prefer phone numbers to be formatted and divided with periods, dashes, parentheses, or spaces?
One of the reasons is readability.
Visually, a whole column with 9 to 12-digit numbers of international or national phone numbers may cause confusion to the person who is reading it.
Another instance, after typing your digits in Excel and clicking the enter key, for example: if your telephone number is ‘00123123123’, the result will be ‘123123123’.
The zero is dropped because here, Excel interprets this not as a phone number but as an ordinary number instead.
Consequently, when the first number/s is/are zero/es, it is interpreted as not significant and therefore should be automatically deleted. It is known as auto-formatting.
In this demo, we will illustrate the following:
- How to format cells having phone numbers in Excel, in such a way that, it is not necessary for you to do manual formatting of every single cell with periods, dashes, parentheses, or spaces.
- How to do a certain number formatting in Excel in such a way that your phone numbers will be aligned to your country or other country’s number formatting in custom.
How to Format Phone Numbers to the Programmed Excel Format
Let’s take the dataset below as our example.
Here, we aim to format phone numbers under Column B.
See the steps below for formatting the phone numbers in Excel:
- Click the cell/s that you want to format.
- See the Menu at the top of your spreadsheet. Under the Home Tab:
- In the ‘Number’ group, see ‘General’ and look for a drop-down arrow beside it.
- Choose ‘More Number Formats’. Or, at the bottom right of the ‘Number’ group, click the small diagonal arrow.
- *Shortcut Tips- Outdo steps 1 and 2 by just a right-click from the selected cell. The result is the same, a ‘Format Cells’ dialog box will pop up.
- Choose the ‘Number’ tab, and from this category, choose the “Special” button.
- On the dialog box’s right side, you can find the formatting list ‘Type’ selections.
- Click ‘Locale (location)’ and select your country. Choose the ‘Phone Number’ option from the list and click OK.
After following all the steps, you will notice that all your selected cells with telephone numbers will automatically change into a formatted phone number with dashes, spaces, or parentheses exactly at their appropriate places.
By doing this, we only modify the way the phone numbers will look on the spreadsheet.
Take note that the values or the type of the original phone numbers don’t actually change.
This means that the cells are not text cells yet even after formatting with dashes, spaces, or parentheses.
Pay attention to the numbers’ alignment. It is known that the alignment of texts is usually on the left side while, conversely, numbers are on the right side.
How to Set Phone Numbers Based on Your Preferred Format
At this point, we now know how to set your phone numbers in general format.
But what if this is not the format you need or want?
Don’t worry! We got your back.
We will teach you how to do formatting in custom.
For instance, you need your numbers to appear in separate dots like 123.123.1234 and not with dashes, spaces, or parentheses.
See the steps below to custom format the phone numbers in Excel:
- Click the cell/s that you want to format.
- See the Menu at the top of your spreadsheet. Under the Home Tab:
- In the ‘Number’ group, see ‘General’ and look for a drop-down arrow beside it.
- Choose ‘More Number Formats’. Or, at the bottom right of the ‘Number’ group, click the small diagonal arrow.
- *Shortcut Tips- Outdo steps 1 and 2 by just a right-click from the selected cell.
- The result is the same, a ‘Format Cells’ dialog box will pop up.
- Choose the ‘Number’ tab, and from this category, choose the “Custom” button.
- Choose a template format that is similar to what you prefer to form such as 0.00. The selected format will be shown in the input box under ‘Type’.
- In the ‘Type’ input box, change the format of your selected numbers to generate the actual format that you want. In this instance, we will modify it with three digits and then dots after, like this, 000\.000\.0000.
- Select the OK button.
These steps will generate your personal type of format. After selecting the OK button, you will have a corresponding result of phone numbers with your desired format.
You may be wondering why before each dot in our format, we put a ‘\’ symbol.
This is particularly because, instead of interpreting dots as a symbol, Excel interprets them as decimals.
We don’t want Excel to start summing up the decimals after the digits of the phone numbers, therefore, we put a symbol of backward slash (‘\’) before every dot.
This backward slash (‘\’) symbol technique is also known as an escape character.
We use this to escape the succeeding character.
This implies that the symbol signals Excel to not identify the succeeding character as a decimal but, instead, it must be associated as a symbol or special character.
Important Note: If you want to use your customized number formats in other workbooks, you have to format them again.
It won’t be available in other Excel workbooks.
US’s Numbers International and Local Code Format
Other countries have different telephone number formats.
Formats for telephone numbers have international standards such as those with International Telecommunication Union issuance.
Also, the various formatting of phone numbering plans differs in different countries.
As stated by the standard of ITU E.123, the international code formatting of the US phone number must be in the form +1 XXX XXX XXXX and the local code formatting should be 0XXX) XXX XXXX.
In addition to that, an informal format that combines the two as +XX (XXX) XXX XXXX also exists.
Here’s what your number format in custom will appear with the two forms:
- In general format code- +00 (000) 0000000
- For national code- (\0000) 0000000
- For international code- +10000000000
+1 connotes the access code internationally for the US.
Aforementioned, for the national code form, we don’t want Excel to start summing up the decimals after the digits of the phone numbers, therefore, we put a symbol of backward slash (‘\’) before every dot.
UK’s Numbers International and Local Code Format
Here’s the format of phone numbers in the UK:
- For provincial areas- (0XXXX) XXXXXX or (0XXX XX) XXXX
- For city areas- (0XXX) XXX XXXX
- For international codes- +44 XXXX XXXXXX
+44 connotes the access code internationally for the UK.
Below is the format in custom for the said forms:
- For provincial areas – (\00000) 000000 or (\000000) 0000
- For city areas – (\0000) 0000000
- For international codes -+440000000000
Let’s also check out a few other countries’ formatting of telephone numbers.
India’s Numbers International and Local Code Format
Here’s the format of phone numbers in India:
- For landline numbers: – XXX-XXXXXXX
- For mobile numbers in local codes – XXXXX-XXXXX
- For mobile numbers in international codes – +91-XXXXX XXXXX
+91 connotes the access code internationally for the UK.
- For landline numbers: 000-0000000
- For mobile numbers in local codes: 00000-00000
- For mobile numbers in international codes:+91-0000000000
Other Countries’ Phone Number Format
With the steps that we illustrated above, we can now easily identify any phone numbers by just:
- Denoting every phone number with a zero ‘0’.
- As they are in the first format, place other symbols.
- Put an escape character, a backward slash symbol, (‘\’) whenever Excel requires the usage of special characters such as zero or dot.
Using the Number Formats in Custom, you can now modify any phone numbers in any Excel number format that you prefer.
Conclusion
In this tutorial, we demonstrated:
- how to format cells having phone numbers in Excel, in such a way that, it is not necessary for you to do manual formatting of every single cell with periods, dashes, parentheses, or spaces.
- how to do a certain number formatting in Excel in such a way that your phone numbers will be aligned to your country or other country’s number formatting in custom.
We hope you found it easy to follow and useful.