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

Excel sometimes fails to differentiate between phone numbers and regular numbers, and as a result, it may display phone numbers in inappropriate formats. This can be a frustrating issue, particularly for those who frequently store phone numbers in Excel.

In this Excel tutorial, I will guide you on how to write phone numbers in a proper format in Excel. I will demonstrate various phone number formats, including phone numbers with country codes, plus (+) signs, and more. Additionally, I will address some critical issues, such as recovering the leading zeros of phone numbers and using the TEXT function to format phone numbers.

In the following image, I presented 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

As there are numerous ways to present phone numbers, we can format phone numbers in Excel in a variety of ways. However, there are two broad categories of methods for formatting and writing down phone numbers in Excel:

1. Using Format Cells dialog box
2. Using Excel Functions

I have discussed them in detail in the following section.

Using Format Cells Dialog Box

In this section, I will show you how to use the Format Cells dialog box to write and store phone numbers in Excel with proper formatting. For illustration, I have taken a dataset containing a list of people and their phone numbers.

As shown above, Excel stores phone numbers as a series of numbers. Our aim is to format them as recognizable phone numbers. To do that, you can follow any of the methods given below as per your requirements:

Using 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:

1. First, select the cells, you want to modify.
2. Then go to the Home tab > Number group > Dialog Box Launcher.
   Consequently, a new dialog box, Format Cells, will appear.
   Note: You can also access this by pressing Ctrl+1.
3. 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;
       As a result, 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.
     • Then press OK.

   

As a result, 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. That means, Excel does not have any dedicated type for that location.

Read More: [Solved!]: Excel Phone Number Format Not Working

Using Custom Formatting

If your local phone number formatting is not available in the Special category of the Format Cells menu, you can create a custom one. By using Custom formatting, we have more flexibility in how we can display the phone numbers.
To use custom formatting to write phone numbers in Excel, follow the steps below:

1. Select the range containing the phone numbers.
2. Then open up the Format Cells dialog box using the shortcut Ctrl+1.
3. In the Format Cells dialog box:
   • • Go to the Number tab;
     • Under Category, click on Custom;
     • Under the Type bar, write down your custom number format code;
       Note: I provided different number format codes in a separate section.
     • Then, click on OK.

   

Thus you can display the phone numbers with your desired custom format.

Some most used custom formats for displaying phone numbers are given below:

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.

Note: # and 0 both indicate the same thing for a number while writing out custom formats.

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 + Sign

To add a plus (+) sign at the start of every phone number, just 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:

Read More: How to Format Phone Number with Dashes in Excel

Using Excel Functions

Aside from all the default and custom formatting discussed above that uses the Format Cells feature, you can also use some Excel functions, especially the TEXT function, to write phone numbers in Excel with proper formatting. You can also format phone numbers with varying lengths using a simple formula that combines the IF, TEXT, and LEN functions.

Below are three examples of using Excel functions to write phone numbers in Excel.

Using TEXT Function for Seven Digit Number

In this example, I will show how to use the TEXT function to write phone numbers with seven digits. For this, I have selected a list of standard seven-digit numbers as shown below:

As you can see, the phone numbers are not properly formatted. Follow the steps below to format those phone numbers using the TEXT function:

1. Select a blank cell and type the following formula: =TEXT(C5, “###-####”)
   Where cell C5 contains the number I want to convert to a phone number, replace cell C5 with where you stored your phone number.
   
2. Press Enter
   Consequently, you will see your phone number formatted as a 7-digit number.
   
3. Now, use Fill Handle to fill out the rest of the list.
   

As a result, you will have a full list of formatted 7-digit phone numbers.

Read More: Excel Formula to Change Phone Number Format

Using TEXT Function for an Area Code

In this example, I will show you how to write phone numbers with area codes in Excel using the TEXT function. For this method, I am using the same dataset.Now, follow the steps below to write phone numbers with country codes in Excel using the TEXT function:

1. Select a blank cell and type the following formula: =TEXT(C5, “541-###-####”)
   Where cell C5 contains the number I want to convert to a phone number and 541 is the country code.
   
2. Press Enter.
3. Now, double-tap the Fill Handle Icon to fill up the rest of the list.
   

As a result, you will have a column full of formatted phone numbers with country codes.

Using TEXT and LEN Functions to Apply Custom Formatting

In real-time data entry, phone numbers can be very diversified. Some are included with an area code, and some aren’t. Most of the time, you can’t even apply the same area code to all the 7-digit numbers in the same column, but you may want to keep the phone numbers with area codes as they are. Mixed Formatting can be an answer to that.

Here is a list of phone numbers that are different in length.Follow the steps below, where I will show you how to apply custom formatting using the TEXT, IF, and LEN functions to handle phone numbers with varying lengths like this:

1. Select a blank cell and type the following formula: =TEXT(C5,IF(LEN(C5)>7,"(###) ###-####","###-####"))
   Cell C5 contains the number I want to convert to a phone number.
   
2. Now press Enter.
   
3. Then double-tap the Fill Handle Icon to fill up the rest of the dataset.
   

In this way, you can format the phone numbers, whether they are seven digits or ten digits.

Read More: How to Format Phone Number with Extension in Excel

How to Write Phone Number Starting with Zero in Excel

Some countries may have phone numbers starting with 0. As Excel takes a phone number as a series of numbers, it can eliminate the zeros at the start of the string because it provides no value to the number. But you may like to keep a 0 before a phone number.

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

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

To write phone numbers starting with 0, you can use any of the following three methods:

Using Apostrophe to Take the Numbers as Text

If you store the numbers as text, Excel does not eliminate the leading 0’s. To store phone numbers as text, follow the steps below:

1. Select a blank cell.
2. Before typing the number, add the Apostrophe sign (‘) and then type out the phone number as you intend it to be.
   
3. 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.

Using CONCAT Function

In this method, we will add the leading zeros of phone numbers after Excel removes them using the CONCAT function. To do that, follow the steps below:

1. Select a blank cell and type the following formula: =CONCAT(“0”, C5)
   Here, cell C5 is the cell where I am taking the reference from; Replace it with your reference.
   
2. Press Enter.
   Consequently, you will have 0 added before the phone number.
   
3. Now, Double-click the Fill Handle Icon.
   

As a result, it will fill up the rest of the values and you will have the full list of phone numbers containing leading zeros.

Using Format Cells Dialog Box

In this method, I will use custom number formatting to display the leading 0s on phone numbers. To show phone numbers that start with 0 using a custom format, follow the steps below:

1. Select the range of cells where you will store phone numbers.
   
2. 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.
   
3. In the Format Cells dialog box:
   • Go to the Number tab;
   • Under Category, click on Custom;
   • In the Type field, type 00000000000
     No. of zero will be the total digit of your phone number, in my case, it is 11.
   • Then, click on OK.

   

4. Now type the phone numbers in a cell without the leading 0 and press Enter.

As a result, Excel will add the leading zeros and display them like this:

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

Read More: How to Format Phone Number with Country Code in Excel

Download Practice Workbook

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

Conclusion

In this article, I showed how to write phone numbers in Excel using built-in number format and different custom formats from the Format Cells menu. I hope the methods discussed above will be useful for you to display phone numbers with appropriate formatting in Excel. If you have any further questions, feel free to ask them in the comment box. Goodbye!

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?

To set data validation for a phone number in Excel, follow the steps below:

1. Select the range of cells where you will store the phone numbers.
2. Now, go to the Data tab > Data Tools group > Data Validation
   As a result, the Data Validation dialog box will open.
3. In the Data Validation dialog box:
   1. Go to the Settings
   2. From the Allow dropdown, choose Custom.
   3. 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.
   4. Click OK

Consequently, 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.

Related Articles

• How to Remove Parentheses from Phone Numbers in Excel

<< Go Back to Phone Number Format | Number Format | Learn Excel