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

Usually, Excel treats phone numbers as just another number entered in a cell unless we modify it or write it manually otherwise. Entering each phone number individually in excel cells for a wide range of rows and columns manually can also be intimidating and tiresome.

Today we will see how to write a phone number or a series of phone numbers using different methods in Excel.


Download Practice Workbook

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


2 Ways to Write Phone Number in Excel

We can categorize all the methods in two sub-sections: formatting cells and using functions. In this article, we will go through each method one by one.

1. Formatting Cells to Write Phone Number

For this set of methods, I have selected the following dataset, which includes each person’s name and phone number.write phone number in excel

As we can see above, Excel enters a phone number as a series of numbers.


1.1 Formatting Cells as Special Category

We can format it by specific country’s phone number format from the Format Cells option.

Steps

  • First, select the cells, you want to modify. In this case, it is the range of cells C5:C11.

  • Then go to the dialog box launcher under the Number group in the Home A new Dialog box, Format Cells, will appear (you can also access this by pressing Ctrl+1 for a shortcut).

  • In the Number tab, go to Special under Category and select Phone Number in the Type Then press OK.

write phone number in excel

You can now see the phone numbers in area code format.

write phone number in excel

Read More: [Solved!]: Excel Phone Number Format Not Working (4 Solutions)


1.2 Formatting Cells as Custom Phone Number

Using the same dataset from above we can use a different formatting approach. With this method, we have more flexibility in terms of modifying the format.

Steps

  • Select the range you want to modify. in this case, it is the range of cells C5:C11.
  • Then open up the Format Cells dialog box either through the Home tab or using the shortcut Ctrl+1.

  • Next in the Number tab, under the Category section, select
  • Under the Type bar write down (###) ###-#### or (000) 000-0000 (for US phone number format).

write phone number in excel

Thus you can achieve the same result as the previous one.

write phone number in excel


1.3 Write Custom Phone Number Format

If you want to format the same number to visually separate it differently, or maybe for a different country. You can also achieve that in Excel.

Steps

  • Select the range you want to modify. In my case, it is the range of cells C5:C11.

  • After that, open up the Format Cells dialog box either through the Home tab or using the shortcut Ctrl+1.

  • Select Custom under the Category.
  • Next, write ###-###-#### or 000-000-0000 in the Type bar and click on OK.

write phone number in excel

Now we can see the phone numbers have changed the way we have written them in the Type bar.

write phone number in excel

Read More: How to Format Phone Number with Dashes in Excel (2 Ways)


1.4 Write Phone Number Starting with Zero

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.

Let’s take a dataset that starts with 0s like this.

write phone number in excel

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

To include 0 in cells one of these methods can be useful.

Method 1: Taking the Numbers as Text (using ‘ sign)

Steps:

  • Select the cell/range of cells.
  • Before starting typing the number add a sign and then type out the phone number as you intend it to be.

  • After typing out the number, press Enter.

write phone number in excel

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

write phone number in excel

Method 2: Using the  CONCATENATE Function:

Steps:

  • Select the cell D5 and type the following formula:
=CONCAT(“0”, C5)
  • In this case, cell C5 is the cell where I am taking the reference from, replace it with your reference.

write phone number in excel

  • Press Enter. You will have 0 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.

write phone number in excel

Method 3: Formatting Cells to Make It Start with 0

Steps:

  • Select the range of cells. In this case, it is from cell C5 to C11.

  • Open up the Format Cell box, either through the dialog box launcher from the ribbon or press Ctrl+1.

  • In the Number tab, select Custom under Category and type 00000000000 (the total digit of your phone number, here it is 11). Click on OK.

write phone number in excel

  • Now type in phone numbers in the cell and you will have a list leading with 0 in each cell.

write phone number in excel


1.5 Write Phone Number Starting with + Sign

Sometimes phone numbers can start with a + sign and are followed by a country code. To enter + sign follow these steps.

Steps

  • Select the columns or ranges you want to include in the sign.

  • Open up the Format Cells box, either through the dialog box launcher or press Ctrl+1.

  • In the Number tab, select Custom under Category and type a + before how you want to modify it in the Type bar (I have selected the “(###) ###-####” category). Press OK.

write phone number in excel

  • Finally, type in the phone numbers in the range and you will have numbers starting with a plus (+) sign.

write phone number in excel

Read More: How to Format Phone Number with Extension in Excel (3 Easy Ways)


1.6 Have the Country Code Included While Writing Phone Number

Let’s take a dataset of phone numbers without any area or country codes.

write phone number in excel

Let us assume that all of these people are from the same area, let’s say Oregon(country code +1, area code 541). If we encounter a group of people having the same codes before their respective numbers, we don’t have to manually type it down every time in Excel.

Follow these steps so that Excel helps fill out the country codes.

Steps

  • Select the cell/range of cells. In this case, I am selecting the range of cells D4:D11 as my cells to include my country code.

  • Then open up the Format Cells box through the dialog box launcher or press Ctrl+1.

  • In the Number tab, select Custom under Category, and in the Type bar type +1-541-000-0000 (you can also type +1-541-###-####).

write phone number in excel

  • Now type in the phone numbers from column C or copy the values and right-click on the formatted cells and select Formulas from Paste Options.

Excel will automatically fill up the area codes and you will have a full formatted phone number.

write phone number in excel

Read More: How to Format Phone Number with Country Code in Excel (5 Methods)


2. Using TEXT Function to Write Phone Number in Excel

Aside from all the formattings, we can also use functions, especially the TEXT function, to write phone numbers in Excel. There are three ways shown below.


2.1 Using TEXT Function for Seven Digit Number

For this, I have selected a list of standard seven-digit numbers as shown.

write phone number in excel

Steps

  • Select the cell D5 and type the following formula:
=TEXT(C5, “###-####”)

(cell C5 contains the number I want to convert to a phone number, replace cell C5 with where you stored your phone number)

write phone number in excel

  • Press Enter and you will see your phone number formatted as a 7-digit number.

write phone number in excel

  • Double click the Formula Handle Bar to fill out the rest of the list.

Now you will have a full list of formatted 7 digit phone numbers.

write phone number in excel

Read More: Excel Formula to Change Phone Number Format (5 Examples)


2.2 Using TEXT Function for an Area Code

For phone numbers consisting of numbers more than seven(with an area code), the TEXT Function can also be applied.

For this method, I am using the same dataset.

Steps

  • Select the cell D5 and type the following formula:
=TEXT(C5, “541-###-####”)
  • After that, press Enter (cell C5 is the cell that contains the number I want to convert, replace it with your reference cell).

write phone number in excel

You can see the area code has been added to the phone number at desired cell.

write phone number in excel

  • Now double-tap the Fill Handle Icon to fill up the rest of the list.

Now, you can have the column full of formatted phone numbers instead of random numbers.

write phone number in excel


2.3 Mixed Formatting Using TEXT and LEN Functions

In real-time data entry, phone numbers can be very much 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 for all the 7-digit numbers in the same column but you may want to keep the phone numbers with area codes as it is. Mixed Formatting can be an answer to that.

Here is a guide for one including the TEXT function, the IF function, and the LEN functions, applying the above-mentioned scenario in this dataset.

Steps

  • Select the cell D5 and type the following formula:
=TEXT(C5,IF(LEN(C5)>7,"(###) ###-####","###-####"))

write phone number in excel

  • Now press Enter.

write phone number in excel

  • Then double-tap the Fill Handle Icon and fill up the rest of the dataset.

Finally, you have formatted phone numbers whether you have seven digits or ten digits numbers.

write phone number in excel


Things to Remember

  • Excel still treats formatted phone numbers (i.e. different area codes added) as the numbers you input.
  • Typing out (‘) before a phone number to keep 0 before it saves the whole number as text.
  • If you want to include dots between numbers in custom format, put it behind “\”.
  • # and 0 both indicate the same thing for a number while writing out custom formats.

Conclusion

These were all the methods you can use to write a phone number in Excel. Hope you enjoyed it and found it useful. For more information and guides keep coming back to Exceldemy.


Related Articles

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo