How to Write Phone Number in Excel (Every Possible Way)

Here’s an overview of how phone numbers are usually displayed in Excel in two columns, in one column without any formatting and in another column with proper formatting.

2 Ways to Write Phone Numbers in Excel

Method 1 – Using the Format Cells Dialog Box

We have a dataset containing a list of people and their phone numbers.

Case 1 – Using the Special Category

Excel offers many built-in phone number formats under the “Special” category in the “Format Cells” menu based on the location settings. This allows you to store phone numbers with the desired formatting according to your local formatting. To use the Special format to display phone numbers, follow these steps:

Select the cells you want to modify.

Go to the Home tab and the Number group, then click on the Dialog Box Launcher.

A new dialog box Format Cells will appear. You can also access this by pressing Ctrl+1.
In the Format Cells dialog box:
- - Go to the Number tab.
  - Under Category, click on Special.
  - Under the Locale(location), select your location from the available drop-down options.
    The available format types for that location will be displayed under the Type menu.
  - Under the Type, select your desired format.
    You can preview the different types under “Sample” to help you choose.
  - Press OK.

The phone numbers will be displayed in the selected type of special formatting.

Note: You may find that some of the options under the Locale(location) do not have any type. Excel does not have any dedicated type for that location.

Case 2 – Using Custom Formatting

Select the range containing the phone numbers.
Open the Format Cells dialog box using the shortcut Ctrl+1.
In the Format Cells dialog box:
- - Go to the Number tab.
  - Under Category, click on Custom.
  - Under the Type bar, choose or insert your custom number format code.
    Note: We provided different number format codes in a separate section.
  - Click on OK.

Here’s the result.

Custom Format 1 – Phone Numbers with Dash

To separate the phone number digits by dashes, you can use the following number format code: ###-###-#### or 000-000-0000
You can change the number of 0’s and #’s based on your preference.

Here is how the phone numbers will be displayed with dashes in the worksheet.

Custom Format 2 – Phone Numbers Separated with Space

To separate the phone number digits by space, you can use the following number format code: ### ### #### or 000 000 0000
You can change the number of 0’s and #’s based on your preference.

Here is how the phone numbers will be displayed with spaces in the worksheet.

Custom Format 3 – Phone Numbers Staring with the + Sign

To add a plus (+) sign at the start of every phone number, put the sign in the number format code like the following: "+"000-000-0000 or "+"###-###-####

Here is how the phone numbers will be displayed with a plus sign at the beginning:

Custom Format 4 – Phone Numbers Separated with Country Code

To display the country code in a phone number, use the following number code in the Type field of the Format Cells window: +1 (000) 000-0000 or +1 (###) ###-####

Here, +1 is the country code and the next 3 digits are area codes and thus separated with a parenthesis.

Here is how the phone numbers will be displayed with country code:

Method 2 – Using Excel Functions

Case 1 – Using the TEXT Function for Seven-Digit Numbers

We have selected a list of standard seven-digit numbers as shown below:

Select a blank cell and insert the following formula: =TEXT(C5, “###-####”)
Cell C5 contains the number we want to convert to a phone number.

Press Enter. You will see your phone number formatted as a 7-digit number.

Use the Fill Handle to fill out the rest of the list.

Here’s the result.

Case 2 – Using the TEXT Function for an Area Code

We are using the same dataset.

Select a blank cell and insert the following formula: =TEXT(C5, “541-###-####”)
Cell C5 contains the number we want to convert to a phone number, and 541 is the country code.

Press Enter.
Double-click the Fill Handle icon to fill up the rest of the list.

You will have a column full of formatted phone numbers with country codes.

Case 3 – Using TEXT and LEN Functions to Apply Custom Formatting

Here is a list of phone numbers that are different in length.

Select a blank cell and insert the following formula: =TEXT(C5,IF(LEN(C5)>7,"(###) ###-####","###-####"))
Cell C5 contains the number we want to convert to a phone number.

Press Enter.

Double-click the Fill Handle Icon to fill up the rest of the dataset.

Here’s the result.

How to Write Phone Numbers Starting with a Zero in Excel

Let’s take a look at a list of phone numbers that start with zeroes like this.

Upon entering the phone numbers, Excel would make it something like this.

Method 1 – Using an Apostrophe to Take the Numbers as Text

Select a blank cell.
Before typing the number, add the Apostrophe sign (‘) and then type out the phone number as you intend it to be.

After typing out the number, press Enter.

In this way, you can type out all the phone numbers with zero as the first value and still keep it.

Method 2 – Using the CONCAT Function

Select a cell and insert the following formula: =CONCAT(“0”, C5)
Cell C5 is the cell where the phone number is.

Press Enter.
You will have a zero added before the phone number.

Double-click the Fill Handle Icon.

It will fill up the rest of the values and you will have the full list of phone numbers containing leading zeros.

Method 3 – Using the Format Cells Dialog Box

Select the range of cells where you will store phone numbers.

Open up the Format Cell box, either through the Dialog Box Launcher from the Number group of the Home tab or by pressing Ctrl + 1.

In the Format Cells dialog box:
- Go to the Number tab.
- Under Category, click on Custom.
- In the Type field, type 00000000000
  The number of zeroes will be the total digits of your phone number.
- Click on OK.

Type the phone numbers in a cell without the leading 0 and press Enter.

Note: Excel still treats formatted phone numbers as the numbers you input. It only displays the number with zero at the beginning.

Download the Practice Workbook

Download this workbook and practice for yourself while going through the article.

Write Phone Number in Excel.xlsx

Frequently Asked Question

Why Does Excel Sometimes Display Phone Numbers in Improper Formats?

Excel may struggle to differentiate between phone numbers and regular numeric data, leading to occasional formatting issues.

What Is the Significance of Proper Phone Number Formatting in Excel?

Proper formatting ensures consistency, making it easier to manage and retrieve phone number data accurately. It also enhances the readability and usability of the spreadsheet.

How Do I Set Data Validation for a Phone Number in Excel?

Select the range of cells where you will store the phone numbers.
Now, go to the Data tab > Data Tools group > Data Validation
As a result, the Data Validation dialog box will open.
In the Data Validation dialog box:
- Go to the Settings
- From the Allow dropdown, choose Custom.
- In the Formula field, write the formula: =AND(ISNUMBER(C3),LEN(C3)=10)
  Replace C3 with the first cell of the selected range and 10 with the number of digits the phone number must contain.
- Click OK
Whenever you try to input phone numbers in the selected range, the cell will only accept values that are 10 digits long and composed only of numbers.