In the modern era, effective communication plays a crucial role and contact information is the heart of its connectivity.
When it comes to managing and analyzing contact information, Excel offers a multitude of benefits. One important task in Excel is to display and format phone numbers in the correct approach, ensuring clarity, consistency, and ease of use.
In this post, we will show you why displaying and formatting phone numbers in Excel is essential and how it can significantly enhance your productivity and overall communication efficiency.
How To Format a Phone Number in Excel
We bet you came across a situation where Excel doesn’t take a leading zero in a phone number. This situation happens to us all once in a lifetime.
You come across a contact from France with the number “0123456789.” However, when you enter this number into Excel, it automatically removes the leading zero and displays it as “123456789.” This seemingly minor alteration can have significant consequences.
Now, picture yourself attempting to call this French contact using the Excel database. Without the leading zero, you may face multiple failed attempts.
Now you may wonder,
How to add a phone number to 0 in Excel?
There are a handful of steps to add a leading zero to a phone number in Excel. For Example using an apostrophe (‘) sign before adding a zero. Other examples include using the special number formatting.
So, Here are the ways to format a phone number in Excel:
1. Format Numbers Using Excel’s Special Number Format
If you are a resident of Portugal, Russia, China, France, or the United States, then there is a pre-format in Excel for phone numbers.
To use the special number format, type your number in the active cell. Press Ctrl+1 (for Windows users) or Cmd+1 (for Mac users) to open the formatting dialog box. Click on the Special Option and adjust the language and location. Select the Phone number type and press OK.
We are residents of the US. So, our phone number will look something like-
(555) 555-1111
You can see the three digits inside the bracket denote the area code and the rest of the seven digits are the local number.
So, what happens if you are outside the US?
No worries, you can customize the number format. Let’s say our telephone number is a 11 digit number 01234567890, but Excel is taking only 1234567890, excluding the front loaded zero. We want to add that particular zero in front.
So, what should we do?
Right-click on that active cell and select the Format Cells option. Click on the custom options. By default, the cell type is saved to Default. Click on the second option (0 Type). Now type the number 0 in the dialog box 11 times (since your telephone number consists of 11-digit). Click OK.
Now, let’s expand our criteria. Let’s say we are non-residents of the US and we want to add the country code in front of our telephone number.
Enter a Mobile Number with a Country Code in Excel
Let’s say our country’s code is (+86), a code from China. And we want to add our 10-digit number alongside.
Select the active cell and type Ctrl+1 to open the Format cells dialog box. Click on the Custom Option and remove the word General from the box. Type the format (+86) and then enter 10 zero’s since our mobile number is 10-digits. Look at the sample text and click Ok if you are done.
In this way you can reformat phone numbers in Excel using the custom formatting.
Let’s look at another alternative method.
2. Use an Apostrophe Symbol Before Typing The Number
The apostrophe symbol allows us to add a zero in front of a phone number. Try using ‘0123456789. This time your number format will be okay. But there’s a problem.
See the yellow mark at the top of the active cell? That’s an error mark.
Although the number was well-formatted, the number was converted to text format. To reverse or fix the issue, you can apply a vba code to convert the text format back to the number format.
3. Apply The TEXT Function
TEXT function converts a numeric value, date, or time into a text string with a specific format. For using this function for formatting phone numbers in Excel, you have to go through a two-step process.
First, gather all the mobile numbers in a column. Then use the TEXT function in a separate column.
Suppose we have our 10-digit phone number format in a regular pattern in Column C3. We will be typing our formula in column D3.
Now, press Enter and use the format painter or drag the cell to autofill the code.
You can also use a combination of IF and LEN Functions but it will be too complicated.
So, next time you want to format any telephone number in Excel, go through these 3 methods and solve your problem easily.
Phone Number Format Not Working in Excel?
The phone number format won’t work because the telephone numbers are stored as text instead of a number format. Click the error warning message at the top of the active cell and select “Convert text to number”.
Then Press Ctrl+1 to Open the formatting dialog box. Select the Custom Option and type (000) 000-0000 based on the telephone number format you provided. Select OK to save changes. Drag the cell column wise to use the text fill function.
FAQ
Question: How to Put a Phone Number in Excel?
Answer: If you want to format phone numbers with dashes, you have to use the Formatting Dialog box. Use the Custom Option and Type (000) 000-0000 to put a phone number with respective dashed format.
Question: What is the formula for mobile number count in Excel?
Answer: Ensure that the mobile number is in text format. Then use the COUNTIF Function. The formula should be like =COUNTIF(range, “##########”). The range denotes the range of cells and the hashtags (#) represents a 10-digit mobile number. Adjust the number of “#” symbols based on the format of mobile numbers in your data.
Conclusion
Applying a phone number formatting is important when you deal with multiple clients across the globe. To communicate with them, you have to use the International country code sometimes.
Recently, I created a Mi account and for a verification code they called me from Russia. You might have seen sophisticated software using country code at first for proper communication.
As a result, proper formatting is required.
