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.

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.

Using Format Cells to Format Phone Number with Country Code

Result:

Finally, you will get the output like the following:

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.

Using Double Quotes to Add Country Code in a Phone Number

Result:

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


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.

using CONCATENATE Function to Add Country Code in Excel

Result:

After that, you will get the formatted phone number containing the 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.

Using NUMBERVALUE Function to Format Phone Number with Country Code

Result:

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

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.

Using IF Function to Add Country Code

Result:

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


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
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo