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.
    Using Format Cells to Format Phone Number with Country Code
  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.

get the output

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.
    Using Ampersand Operator to Format Phone Numbers with Country Code
  3. Drag down the Fill Handle to copy the formula to the rest of the cells.
    Drag down the Fill Handle to copy the formula

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

Using Ampersand Operator to Format Phone Numbers with 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.
    using CONCATENATE Function to Add Country Code in Excel
  3.  Drag down the Fill Handle to copy the formula to the rest of the cell.

All the selected phone numbers will be formatted.
show the Format Phone Number with Country Code

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.
    Opening the Visual Basic window from the developer tabAlternatively, 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.
    Inserting New 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

    VBA Code for Custom 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.
    Using the Custom Function to get the Formatted Phone Number with Country Code
  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.
    Using Fill Handle to Copy the Formula

All the phone numbers will get formatted.

Using Custom Function to Format Phone Numbers with Country Code


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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo