How to Add Country Code in Excel

When contacting individuals from different countries, you must remember to include their country codes. This is because country codes guide phone companies in routing your phone calls.

With that said, there isn’t exactly a function to help you assist with adding country codes in Excel. However, manually inserting codes for each contact is a drawn-out task I highly recommend you avoid. 

Before you go through the automated methods, if you’re interested in manually entering country codes in your spreadsheet, make sure your cell is formatted as Text. If you don’t, Excel will automatically remove the plus sign (+) in front of the country code as it will recognize it as a number value.

Use the Ampersand Operator

This method works best when you have to add the same country codes for all contacts.

We will be using the ampersand operator (&) to merge the contact number with our country code. For this example, let’s assume that we want to combine the country code for the United States of America (USA), +1 with our contact numbers.

In range B2:B11, we have 10 contact numbers with no country code. Let’s use the ampersand operator to change this! In cell C2, I entered:

="+1-"&B2

Use CONCAT and MID

In this method, we will be adding the country code in a different format using the CONCAT and MID functions.

In the previous method, our value followed a +1-XXXXXXXXXX format. Now, if you want to add country codes in the +1(XXX) XXXXXXX format, you’ll have to extract the first three and remaining values from the contact number using the MID function and merge them using the CONCAT function.

Let’s use the same data set as before. In cell C2, we entered our formula using CONCAT and MID in the following way:

=CONCAT("+1","(",MID(B2,1,3),")"," ",MID(B2,4,7))

Change Cell Format

You can also customize your cell according to the country code you wish to enter. We will be using the same +1(XXX) XXXXXXX format as before in this method as well. However, you can change the formatting to whichever format you wish.

1. Select the cell/range with your contacts.
2. On your keyboard, press Ctrl + 1.
3. From the sidebar, select Custom.
4. Under Type, enter the following value:

+1-(000) 0000000

5. Click OK.

Create a Function Using VBA

Earlier, I mentioned before that there’s no exact function to insert country codes in Excel. Well, let’s put on our programming hats and change that for us!

Excel, including other applications from Microsoft 365 uses the Visual Basic for Application (VBA) language. Therefore, you can create your own functions and even macros using the same language. If you’ve never done something like this before, make sure you enable the Developer tab from Excel Options.

1. Go to the Developer tab.
2. Select Visual Basic.
3. On the VBA Editor, go to Insert > Module.
   
4. In the window, enter the following code:

Function CCode(Contact As String, Code As String) As String
Final = "+" & Code & "-" & "(" & Mid(Contact, 1, 3) & ")" & " " & Mid(Contact, 4)
CCode = Final
End Function

5. Go to your spreadsheet, and then enter the formula in the following format:

=CCode("your_contact","country_code")

Add Codes for Multiple Countries

While it is quite simple to merge the same country code to multiple contact numbers, the same task can become a chore when there are numerous countries.

If you’re dealing with more than one country, you can use the IFS function to create conditions for entering your country code. 

If you’re unfamiliar with the IFS function, it’s basically the IF function with multiple logic tests and results. Here’s the syntax the IFS function follows when constructing a formula:

=IFS(logic_test1,value_if_true1,[logic_test2,value_if_true2],[logic_test3,value_if_true3],...)

In this sheet, we have the contact numbers of three countries, the United States, Japan, and India. We can identify the country using the value in range C2:C11, which has the contact’s country name.

Step 1: Create a Table with Country Code

Firstly, let’s create a table with the country’s name and country code. We will be using the cells in this table as references, as opposed to hard-coding them in our formula.

Step 2: Enter Formula

Now that we have our reference table ready, let’s create our conditional formula to add country codes. In cell D2, I entered the following formula:

=IFS(C2=$G$7,CONCAT($H$7,B2),C2=$G$8,CONCAT($H$8,B2),C2=$G$9,CONCAT($H$9,B2))

While analyzing this formula, notice how some references have the dollar sign ($). These references are called absolute references. You can switch between relative and absolute references using the F4 key on your keyboard.

Especially in this formula, the type of reference you choose will greatly alter your result. Make sure that the cells you’re referencing from the data table we just created have dollar signs in front of both the row and column values.