Abrar-ur-Rahman Niloy
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.
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.
You can now see the phone numbers in area code format.
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).
Thus you can achieve the same result as the previous one.
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.
Now we can see the phone numbers have changed the way we have written them in the Type bar.
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.
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.
In this way, you can type out all the numbers with zero as the first value and still keep it.
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.
- 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.
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.
- Now type in phone numbers in the cell and you will have a list leading with 0 in each cell.
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.
- Finally, type in the phone numbers in the range and you will have numbers starting with a plus (+) sign.
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.
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-###-####).
- 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.
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.
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)
- Press Enter and you will see your phone number formatted as a 7-digit number.
- 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.
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).
You can see the area code has been added to the phone number at the desired cell.
- 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.
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,"(###) ###-####","###-####"))
- Now press Enter.
- 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.
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.
