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

Get FREE Advanced Excel Exercises with Solutions!

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.


We can categorize all the methods into 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 in Excel

For this set of methods, I have selected the following dataset, which includes each person’s name and phone number.dataset to 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.

formatting cells to write phone number in excel

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

phone number in excel

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


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 custom phone number in excel

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

write phone number in excel


1.3 Writing 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.

writing custom phone number with 0 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


1.4 Writing 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. But you may like to keep a 0 before a phone number.

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

phone numbers list

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 0 in phone number as text

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

phone numbers starting with 0


Method 2: Using the  CONCATENATE Function

In this context, we will use 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.

using concatenate to 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.

phone numbers starting with 0


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 by pressing 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.

formatting cells

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

phone numbers starting with zero


1.5 Writing 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 by pressing 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.

preparing cells to write phone numbers

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

custom phone numbers

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


1.6 Including Country Code While Writing Phone Number

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

dataset

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-###-####).

formatting cells for area code

  • 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 numbers with area code in excel

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


2. Using Excel TEXT Function to Write Phone Number

Aside from all the formatting, 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.

dataset to write phone numbers

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)

text function to write phone numbers

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

phone number with text function

  • 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


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).

text function with area code

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

phone number after text function with area code

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

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

write phone number in excel


2.3 Custom 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 function, applying the above-mentioned scenario in this dataset.

Steps

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

text, if and len function to write phone number in excel

  • Now press Enter.

writting first mixed format phone number

  • 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-digit or ten-digit numbers.

mixed format phone numbers with formula


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.

Download Practice Workbook

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


Conclusion

These were all the methods you can use to write a phone number in Excel. Hope you enjoyed it and found it useful.


Related Articles

Abrar-ur-Rahman Niloy
Abrar-ur-Rahman 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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo