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.

Read More: Excel Formula to Change Phone Number Format

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

• How to Format Phone Number with Extension in Excel
• How to Format Phone Number with Dashes in Excel
• How to Remove Parentheses from Phone Numbers in Excel

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