# How to Format Phone Number with Country Code in Excel (4 Methods)

### Method 1 – Using Format Cells Dialog Box

1. Select the range containing phone numbers.
2. Press Ctrl+1 to open the Format Cells dialog box.
3. In theÂ  Format Cells dialog box:
• Go to Number tab > Custom category.
• In the Type field, write the following number format code:
`+1 (000) 000-0000`
Here, replace +1 with your dataset’s country code and add or subtract 0 based on the number of digits in a phone number.
• Click OK.

The phone numbers will be properly formatted with the country code.

Read More: How to Write Phone Number in Excel

### Method 2 – Using Ampersand Operator

1. Select a blank cell and write the following formula:
`="+1"&C5`
Here, replace C5 with the cell containing the phone number and +1 with the country code.
2. Press Enter.
The phone number will be formatted with its country code.
3. Drag down the Fill Handle to copy the formula to the rest of the cells.

All the phone number will be formatted with the country code.

Note: If your dataset contains any blank cells, you can use the IF function to ignore the blank cells. Here is the formula:
`=IF(C5<>"","+1"&C5,"")`

Read More: Keep 0 Before a Phone Number in Excel

### Method 3 – Using CONCATENATE Function

1. Select a blank cell and write down the following formula:
`=CONCATENATE("+1",C5)`
Or
`=CONCAT("+1",C5)`
Here, replace C5 with the cell containing the phone number and +1 with the country code.
2. Press Enter.
The phone number will be formatted with its country code.
3. Â Drag down the Fill Handle to copy the formula to the rest of the cell.

All the selected phone numbers will be formatted.

### Method 4 – Applying Custom Function with VBA

1. Open the Microsoft Visual Basic for Applications window by going to the Developer tab> Visual Basic.
Alternatively, use the ALT+F11 shortcut keys to open the VB Editor.
2. In the Microsoft Visual Basic for Applications window, insert a new module by going to Insert > Module.
3. In the new module, paste the following code:
``````Function Add_CCode(Phone_Number As Range, Country_Code As String) As String
Formatted_Ph_Number = "+" & Country_Code & "-" & "(" & Mid(Phone_Number, 1, 3) & ")" & " " & Mid(Phone_Number, 4)
Add_CCode = Formatted_Ph_Number
End Function``````

4. Return to your worksheet and use the following formula in a blank cell:
`=Add_CCode(C5,1)`
Where C5 is the cell containing the Phone Number, and 1 is the country code.
5. Press Enter to see the result.
6. If you have the same country code for the rest of the phone numbers, you can use the Fill Handle to get the formatted phone numbers for the remaining cells.

All the phone numbers will get formatted.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

## Frequently Asked Question

### Are There Benefits to Adding Country Codes Beyond Visual Appearance?

Absolutely! Beyond appearance, including country codes allows for practical tasks like sorting, filtering, and organizing data efficiently.

### How Do I Format Phone Numbers with Country Codes in Excel Without Using Formulas?

Easy! Utilize the Format Cells menu. It’s a straightforward way to make your numbers look good without diving into complex formulas. To learn how to do it, go to the first method that discusses the process in detail.

### How Do I Add 0 Before Mobile Number in Excel?

You can use custom formatting to add the leading zeros before mobile numbers. To do that, follow the steps below:

1. Select the cells that you want to format.
2. Press Ctrl+1 to open the Format Cells dialog box.
3. In the Format Cells dialog box, go to Number tab > Custom category > Type box and type: 000000
Each “0” in this code represents a digit. If you have a 5-digit phone number like 12345, typing six “0â€™s” ensures that leading zeros are displayed, like 012345.
4. Click OK to apply the formatting.

## Related Articles

<< Go Back to Phone Number Format | Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

2 Comments
1. Reply
Robert William Walker Aug 25, 2022 at 10:32 AM

This seems to apply only to one country code. What if you have different country codes?

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

Advanced Excel Exercises with Solutions PDF