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

In our everyday routines, we often need to store phone numbers for various purposes, such as keeping track of business contacts, maintaining a customer database, or organizing service appointments. Though Excel is a fantastic tool for managing data, it doesn’t automatically format phone numbers with country codes properly.

In this article, I will walk you through the process of formatting phone numbers with country codes. Here, I will guide you on using the Format Cells dialog box, the Ampersand operator (&), and a few handy Excel functions to format phone numbers with country codes.

Check out the image below to see how phone numbers appear in Excel when formatted with country codes.

What is Country Code in Phone Numbers?

A country code in phone numbers is like a little tag that tells us which part of the world a number belongs to. It’s usually written with a plus sign (+) followed by a set of digits. Now, why bother with these codes? Well, including country codes is essential to avoid any confusion. Imagine having phone numbers from different countries all mixed up – it would be like a jigsaw puzzle without the picture on the box! Country codes help Excel (and us) by organizing the numbers neatly and ensuring we know exactly where each call is coming from. So, they’re like the postal codes of the phone world, making sure every call finds its way home.

4 Methods to Format Phone Number with Country Code in Excel

You can use four efficient methods to format phone numbers with country codes in Excel such as: Using the Format Cells dialog box, Ampersand operator (&), the CONCATENATE function, and a VBA custom function. Let’s explore the methods one by one.

Using Format Cells Dialog Box

If you already have a list of phone numbers from a specific country that does not include the country code, you can format those phone numbers with the Format Cells dialog box so that they appear with the country code without actually entering the code. You will have to use a custom format here according to your country code.

To format phone numbers with country codes using the Format Cell dialog box, follow the steps below:

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.

   

As a result, the phone numbers will be properly formatted with the country code clearly visible.

Please note that the phone numbers have only changed in appearance and are still the same. However, if you need to format phone numbers from different countries, you must format them separately with the appropriate country code for each country.

Read More: How to Write Phone Number in Excel

Using Ampersand Operator

In the previous method, the phone numbers were modified only in terms of their appearance, without actually including the country codes. However, this approach may not be ideal for tasks like sorting and filtering, where the actual country code is necessary. In such scenarios, it is more practical to explicitly add the country code.

In this method, I will use the ampersand operator (&) to explicitly add the country code with phone numbers. To do this, follow the steps below:

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.
   As a result, you will get the phone number formatted with its country code.
   
3. Now drag down the Fill Handle to copy the formula to the rest of the cells.
   

As a result, you will get the formatted phone number containing 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

Using CONCATENATE Function

Instead of using the ampersand operator, you can also join the country code with a phone number using either CONCATENATE or CONCAT function. The CONCAT function is the updated version of CONCATENATE function and is only available in Excel 2016 or later versions.

To format phone numbers with country codes using the CONCATENATE/CONCAT function, follow the steps below:

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.
   As a result, you will get the phone number formatted with its country code.
   
3.  Now, drag down the Fill Handle to copy the formula to the rest of the cell.

As a result, you will get the formatted phone number containing the country code.

If the phone numbers are from different countries, you must adjust the formula and replace the “+1” with the appropriate country code.

Read More: Excel Formula to Change Phone Number Format

Applying Custom Function with VBA

If you frequently need to format phone numbers with country codes, you can create a VBA custom function that adds and formats the appropriate country codes. In this method, I will use a custom function named Add_CCode, which takes the phone number and country code as arguments and returns them with proper formatting.

To learn how to create a custom function to format phone numbers with country codes, follow the steps below:

1. First, 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. Now, 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.
   

As a result, you get all the phone numbers formatted with country codes.

Remember, if the phone numbers are from different countries, then you need to manually apply the formula by adjusting the second argument (country code). In this way, you can format the phone numbers with country codes for different countries quickly and efficiently.

Download Practice Workbook

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

Conclusion

In this article, I have shared some useful methods for formatting phone numbers in Excel with the country code. Additionally, I have discussed the advantages and disadvantages of each method. I hope that these tips will prove helpful for you when you are storing phone numbers. If you have any further questions or suggestions, please feel free to post them in the comment section.

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

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. Easy, right?
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