Saquib Ahmad Shuvo
If you are searching for some special tricks to format phone numbers with country codes, you are in the perfect place. In this article, we’ll discuss five special tricks to format phone numbers with country codes in Excel. Let’s follow the complete guide to learn all of this. In this tutorial, you will learn to format phone number with country code in Excel.
How to Format Phone Number with Country Code in Excel: 5 Methods
Here, we have a dataset containing the phone numbers of some people of United Company. Now, we want to have the country code +1 (which is a country code for the USA) before the phone numbers of the FORMATTED NUMBER column.
In the following sections, we will use five methods to format these phone numbers with country codes.
1. Using Format Cells to Format Phone Number with Country Code
To add country code in the FORMATTED NUMBER column, you have to follow the following steps.
Steps:
- Select the phone numbers from the range of cells C5:C12.
- Press Ctrl+1. When the Format Cells dialog box opens, choose Custom And type +1 (000) 000-0000, then click on OK.
Result:
Finally, you will get the output like the following:
Read More: How to Write Phone Number in Excel
2. Using Double Quotes to Add Country Code in a Phone Number
It is the quickest way to add a country code to a phone number. In this method, you have to add +1( country code for the USA) before phone numbers.
Insert the following Formula in cell D5:
="+1"&C5In this formula, cell C5 is the phone number.
- Press Enter and then drag down the Fill Handle icon.
Result:
After that, you will get the formatted phone number containing the country code.
Read More: Keep 0 Before a Phone Number in Excel
3. By Using CONCATENATE Function to Add Country Code in Excel
If you want to add a formula to add the country code before the phone number, then you should choose the CONCATENATE Function.
So, in our dataset, the formula is the following for cell D5.
=CONCATENATE("+1",C5)In this formula, cell C5 is the phone number, and cell D5 is the formatted phone number.
- Press Enter and then drag down the Fill Handle icon.
Result:
After that, you will get the formatted phone number containing the country code.
In the end, you can see our method worked successfully to format phone numbers with country codes in Excel.
Read More: Excel Formula to Change Phone Number Format
4. Using NUMBERVALUE Function to Format Phone Number with Country Code
The NUMBERVALUE function basically converts text to numbers in a locale-independent manner.
Syntax :
In our dataset, we use the following formula in cell D5.
=NUMBERVALUE("+1"&C5)In this formula, cell C5 is the phone number, and cell D5 is the formatted phone number.
- Press Enter and then drag down the Fill Handle icon.
Result:
After that, you will get the formatted phone number containing the country code.
Read More: [Solved!]: Excel Phone Number Format Not Working
5. Using IF Function to Add Country Code
In our dataset, we would use the IF function to add country code.
In our dataset, we use the IF Function in this way.
=IF(C5<>"","+1"&C5,"")Here, in this formula, cell C5 is the phone number.
- Press Enter and then drag down the Fill Handle icon.
Result:
After that, you will get the formatted phone number containing the country code in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
That’s the end of today’s session. I strongly believe from now you may format phone numbers with country codes. If you have any queries or recommendations, please share them in the comments section below.
This seems to apply only to one country code. What if you have different country codes?
Greetings, WALKER!
Your question is greatly appreciated.
The process is the same if you have different country codes. Just change the country code number. For instance, if you want to change a telephone number to a UK format.
Firstly, select the phone numbers from the range of cells. Next, press Ctrl+1. Select Custom from the Format Cells dialog box, then type +44 (000) 000-0000. Click on OK.