Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

If you are searching for some special tricks to format phone number with country code, you are in the perfect place. In this article, we’ll discuss five special tricks to format phone numbers with country codes in excel. Let’s follow the complete guide to learn all of this.  In this tutorial, you will learn to format phone number with country code in Excel.


Download Practice Workbook

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


5 Methods to Format Phone Number with Country Code in Excel

Here, we have a dataset containing the phone numbers of some people of United Company. Now we want to have the country code +1 (which is a country code for the USA) before the phone numbers of the FORMATTED NUMBER column.

 Format Phone Number with Country Code in Excel

In the following sections, we will use five methods to format these phone numbers with country codes.


1. Using Format Cells to Format Phone Number with Country Code

To add country code in the FORMATTED NUMBER column, you have to follow the following steps.

Steps:

  • Select the phone numbers from the range of cells C5:C12.

Using Format Cells to Format Phone Number with Country Code

  • Press Ctrl+1. When the Format Cells dialog box opens, choose Custom And type +1 (000) 000-0000, then click on OK.

click on Custom option to Format Phone Number with Country Code

Result:

Finally, you will get the output like the following:

get the output

Read More: How to Write Phone Number in Excel (Every Possible Way)


2. Using Double Quotes to Add Country Code in a Phone Number

It is the quickest way to add a country code to a phone number. In this method, you have to add +1( country code for the USA) before phone numbers.

Insert the Following Formula in cell D5:

="+1"&C5

In this formula, cell C5 is the phone number.

Using Double Quotes to Add Country Code in a Phone Number

  • Press Enter and then drag down the Fill Handle icon.

Result:

After that, you will get the formatted phone number containing the country code.

show the Format Phone Number with Country Code in Excel


3. By Using CONCATENATE Function to Add Country Code in Excel

If you want to add a formula to add the country code before the phone number, then you would choose the CONCATENATE Function.

So, in our dataset, the formula is the following for cell D5.

=CONCATENATE("+1",C5)

In this formula, cell C5 is the phone number and cell D5 is the formatted phone number.

using CONCATENATE Function to Add Country Code in Excel

  • Press Enter and then drag down the Fill Handle icon.

Result:

After that, you will get the formatted phone number containing the country code.

show the Format Phone Number with Country Code

In the end, you can see, our method worked successfully to format phone number with country codes in Excel.


4. Using NUMBERVALUE Function to Format Phone Number with Country Code

The NUMBERVALUE function basically converts text to number in a locale-independent manner.

Syntax :

=NUMBERVALUE(Text, [Decimal_separator], [Group_separator ])

In our dataset, we use the following formula in cell D5.

=NUMBERVALUE("+1"&C5)

In this formula, cell C5 is the phone number and cell D5 is the formatted phone number.

Using NUMBERVALUE Function to Format Phone Number with Country Code

  • Press Enter and then drag down the Fill Handle icon.

Result:

After that, you will get the formatted phone number containing the country code.

show the output

Read More: [Solved!]: Excel Phone Number Format Not Working (4 Solutions)


5. Using IF Function to Add Country Code

In our dataset, we would use the IF function to add country code.

In our dataset, we use the IF Function in this way.

=IF(C5<>"","+1"&C5,"")

Here, in this formula, cell C5 is the phone number.

Using IF Function to Add Country Code

  • Press Enter and then drag down the Fill Handle icon.

Result:

After that, you will get the formatted phone number containing the country code in Excel.

get the output


Conclusion

That’s the end of today’s session. I strongly believe from now you may format phone numbers with country codes. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Saquib

Saquib

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

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

ExcelDemy
Logo