Tag: Excel TEXT

Sometimes when we import phone numbers from external sources, the formatting of the phone numbers is removed. Even if in some situations the phone numbers are not imported from external resource, sometimes the phone numbers in worksheet are not displayed in the correct format, which is very common in daily work. When we are faced with a string of unformatted phone numbers, we find it very confusing and difficult to see the layout of the phone number. Therefore, it is very important for us to learn how to format phone numbers in Excel worksheets and Google worksheets.

In today’s tutorial, we will introduce how to format phone numbers through Excel built-in cell format function, Excel function or formula. These functions can be applied directly to Microsoft Excel worksheet and Google sheet to format phone numbers. We can also customize the formatting in excel worksheets and google worksheets to format different phone number formats depend on different countries.

In addition to how to format phone numbers, we also introduce the phone number formats of some countries, including USA, UK, France, China, Brazil, etc. Each country offers two phone number formats depending on the international or domestic call you are dialing from. We also list the ways to format phone numbers for these countries.

Format Phone Numbers to Built-in Excel Format

Let’s start with the built-in “Format Cells” function in Excel to convert number format to phone number format.

In excel, there is a self-contained phone number format. This feature is available in Excel worksheets, Google worksheets. In worksheet, there is a special format in “Format Cells” options to format phone numbers according to the position of the numbers. The layout of phone numbers may vary from country to country in the number of digits and symbols, such as the placement of parentheses and hyphens. Here are the specific steps to perform this operation.

Below steps are operated on Excel 2016 for Mac.

STEP 1# Select the cell or a list that needs to be formatted.

STEP 2# In the Home tab (Home tab is displayed on the top in the ribbon), select the Format drop-down menu, then select “Format cells…“. You can also just right-click on your selection and select “Format Cells…” from the pop-up menu.

STEP 3# In the pop-up “Format Cells” dialog box, select the fist “Number” tab, click “Special” from the Category list, then you can see a list of formatting types on the right side of the dialog box.

STEP 4# Select the “Phone Number” option from the list. Then you can see that your phone number is formatted correctly in Sample field. In our example, 2134567890 is formatted to (213)456-7890.

STEP 5# Click “OK” to apply this format to all phone numbers in the selected range.

It should be noted that the format of the cell can not be text, this function does not work well for text. If there is a leading “0”, we need to remove this “0” first, and then use custom formatting to add “0” back. Let’s look at the following example of custom formatting.

Format Phone Numbers to A Custom Format

If you do not find a preset format that meets your requirements in Excel worksheet or Google worksheet, you can set up a custom format via “Format Cell” function to display the numbers as phone numbers. Again, we still use the “Cell Format” setting to apply custom format to the numbers. Here are the steps for formatting a phone number using custom format.

STEP 1# Select the cell or a list that needs to be formatted.

STEP 2# In the Home tab (Home tab is displayed on the top in the ribbon), select the Format drop-down menu, then select “Format cells…“. You can also just right-click on your selection and select “Format Cells…” from the pop-up menu.

STEP 3# In the pop-up “Format Cells” dialog box, select the fist “Number” tab, click “Custom” from the Category list, then you can see a list of formatting types on the right side of the dialog box. Some types are preset; some are saved in daily work per your demand.

STEP 4# You can select a built-in format that is most similar to the one you want to create. If there is not a similar one, you can type a proper phone number format you want in “Type” textbox. Then custom phone number format is reflected in Sample field. Note that all symbols entered, including spaces, will be reflected in the phone number format.

Here we enter (###) ###-#### to reflect phone number. Each # represents a number.

STEP 5# Click “OK” to apply this format to all phone numbers in the selected range.

Custom format helps us format phone numbers without restrictions. Using a good concatenation symbol, we can enter any phone number format we want. We can also enter zero and plus sign directly into the custome phone number format.

Phone Numbers with Preceding “+” Sign or Leading “0”

You can add leading zero in parentheses:

Enter (0###) ###-#### in Type:

You can add leading zero outside the parentheses:

Enter 0 (###) ###-#### in Type:

You can add +1 in front of all phone numbers:

Enter (+1) (###) ###-#### in Type:

Using the types listed above we can add a leading 0 or country code (or area code) to a phone number if the original phone number does not have a leading 0 or code.

Format Phone Number with Excel TEXT Function

The text function can quickly handle phone numbers with the same format.

TEXT function syntax:

=TEXT(value, format_text)

Format a phone number

In the cell just next to phone number, enter =TEXT(A1,”(###) ###-####”).

Format a phone number table

In my example, the phone numbers are saved in a table, so when you enter the TEXT function in B2, the value parameter is the phone numbers table, and when you press ENTER to run TEXT, a new table is created.

Format phone numbers with different formats (different lengths)

If phone numbers of different lengths are mixed together, the TEXT function alone cannot help us format the phone numbers, so we need the help of the IF function. We can first sort the phone numbers by region or length of the phone number, and then use the IF and TEXT functions to format the phone numbers. Note that this formula can only format two types of phone numbers.

For example, the length of the phone number is different. Some contain 11 digits and some contain 10 digits. For phone number with 11 digits, format text to “(###) # ###-####”, for phone number with 10 digits, format text to “####-###-###”.

=TEXT([@[Phone Number]],IF(LEN([@[Phone Number]])<11,"####-###-###","(###) # ###-####"))

List of International Phone Number Format Standards

International standards for phone number formats are issued by the International Telecommunication Union. Each country and region has its own way of writing phone numbers. Even for the same phone number, the format of the phone number is different for domestic calls and international calls.

A standard phone number should be “+ (country code) (area code) phone number”. For example, +(1)(425) 555-0100 is the phone number for the U.S. If you are calling a U.S. phone number, their phone number starts with +1, followed by the correct 10 digits of the phone number (the first three are the area code). The other countries follow the similar rules.

When dialing an international call, you need to add 001 (the U.S. international call area code) plus the U.S. local area code in front of phone number or cell phone number.

For example, in the dialing screen, type 001 860 xxxx xxxx, you can call Connecticut from abroad. Here 00 is the international call access code, the leading 0 is usually ignored in writing.

US Phone Number Format

International Call +1 860 XXX XXXX

Domestic Call +0860 XXX XXXX

(The prefix code 860 is the area code for Connecticut, USA)

UK Phone Number Format

International Call +44 20 XXXX XXXX

Domestic Call +020 XXXX XXXX

(The prefix code 20 is the area code for London, UK)

Australia Phone Number Format

International Call +61 2 XXXX XXXX

Domestic Call +02 XXXX XXXX

(The prefix code 2 is the area code for Sydney, Australia)

People’s Republic of China Phone Number Format

International Call +86 10 XXXX XXXX

Domestic Call +010 XXXX XXXX

(The prefix code 10 is the area code for Beijing, RPC)

Japan Phone Number Format

International Call +81 3 XXXX XXXX

Domestic Call +03 XXXX XXXX

(The prefix code 3 is the area code for Tokyo, Japan)

South Korea Phone Number Format

International Call +82 2 XXX XXXX

Domestic Call +02 XXX XXXX

(The prefix code 2 is the area code for Seoul, South Korea)

India Phone Number Format

International Call +91 80 XXXX XXXX

Domestic Call +080 XXXX XXXX

(The prefix code 80 is the area code for Bangalore, India)

Related Functions

• Excel IF function
  The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
• Excel Text function
  The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…