How to Add Country Code to Phone Automatically in Excel

Do you need to add country codes to a long list of phone numbers in Excel? Doing this manually can be time-consuming and prone to error. However, there is a way to automate the process using a formula in Excel. In this tutorial, we’ll show you two methods for adding country codes to phone numbers automatically in Excel.

Method 1

Step 1: Determine the Country Code The first step is to determine the country code you want to add to the phone numbers. You can find this information easily by searching on the internet. For example, if you want to add the country code for the United Kingdom, it is “+44”.

Step 2: Create a new column next to the column that contains the phone numbers. This is where we will add the country code.

Step 3: Enter the Formula In the first cell of the new column, enter the following formula: =IF(LEFT(B1,1)=”+”,C2,”+1″&B2)

This formula checks whether the first character of the phone number is already a plus sign, which indicates that the country code is already included. If it is, the formula leaves the phone number as it is. If it isn’t, the formula adds the country code before the phone number.

Note that in the above screenshot the phone numbers are in column B starting from the second row (i.e., cell B2). Adjust the formula accordingly if your data is located elsewhere.

Step 4: Copy the Formula Now copy the formula to the entire column by clicking on the bottom right corner of the cell and dragging it down. This will apply the formula to all the cells in the column.

Step 5: Check the Results After copying the formula to all the cells, the new column should now contain the phone numbers with the country code added automatically. Check the results to ensure that the country code has been added correctly.

Method 2:

Step 1: Right click on the column where you want to display the phone number.

Step 2: Select Format Cells from the menu list.

Step 3: Under Category select Custom and enter +44 000-000-000 in Type field. Then Ok to save the changes.

Step 4: Now entering a phone number like “1234567890” will be automatically changed to “+44 1234-567-890”

That’s it! You have successfully added country codes to phone numbers automatically in Excel. These methods can save you a lot of time and reduce the chance of errors in your data.