How to Use Phone Number Format in Excel (8 Examples)

If you are looking for some of the easiest ways for Excel phone number format, then you are in the right place. By following this article, you will be able to easily change the format of numbers into phone number format easily. So, let’s get started with the main article.

Download Workbook


8 Ways to Use Phone Number Format in Excel

Here, we have used the following table for demonstrating the examples of using phone number format in Excel.
For creating the article, we have used Microsoft Excel 365 version, you can use any other versions according to your convenience.

Excel phone number format


Method-1: Using Phone Number Option for Having Excel Phone Number Format

Here, we have the contact numbers of the employees of a company in general number format. But we can convert them into a standard format so that you can easily understand that it is a phone number. For this purpose, we will use here the inbuilt Phone Number option of Excel.

Phone Number Option

Steps:
➤ Select the range of cells where you want to have the phone number format
➤ Click the Dialog Box Launcher besides the Number group under the Home tab

Phone Number Option

After that, the Format Cells dialog box will appear
➤ Select the followings

Category → Special
Type → Phone Number

➤ Press OK

Phone Number Option

Result:
In this way, you will get your desired phone number format where the first three digits in brackets represent the area code.

Phone Number Option

Read More: How to Format Number with VBA in Excel (3 Methods)


Method-2: Using Zip Code+4 Option for Excel Phone Number Format

You can also use the inbuilt Zip Code + 4 option for having the phone number formats of the contact numbers which are in general format.

Zip Code + 4 option

Steps:
➤ Select the phone numbers of the Contact Number column
➤ Click the Dialog Box Launcher besides the Number group under the Home tab

Zip Code + 4 option

Then, the Format Cells dialog box will pop up
➤ Choose the followings

Category → Special
Type → Zip Code + 4

➤ Press OK

Zip Code + 4 option

Result:
In this way, you will get your desired phone numbers in the Zip Code + 4 format

Zip Code + 4 option

Read More: Keep 0 Before a Phone Number in Excel (6 Methods)


Method-3: Using Social Security Number Option for Excel Phone Number Format

In this section, we will use the Social Security Number option for changing the numbers in general format into social security numbers.

Excel phone number format

Steps:
➤ Select the range of cells where you want to have the phone number format
➤ Click the Dialog Box Launcher besides the Number group under the Home tab

Social Security Number Option

After that, the Format Cells dialog box will appear
➤ Select the followings

Category → Special
Type → Social Security Number

➤ Press OK

Social Security Number Option

Result:
Then, you will get the following contact numbers into the Social Security Number format.

Social Security Number Option

Read More: How to Use Number Format Code in Excel (13 Ways)


Method-4: Using Custom Number Formatting

Suppose, you want to have the country code +1 ( country code for the USA) before the phone numbers of the Contact Number column, and to have this customized format you can follow this method.

custom number formatting

Steps:
➤ Select the phone numbers of the Contact Number column
➤ Click the Dialog Box Launcher besides the Number group under the Home tab

custom number formatting

Then, the Format Cells dialog box will pop up
➤ Choose the followings

Category → Custom
Type → +1 (000) 000-0000 (+1 will be added prior to the remaining 10 digits of the contact numbers)

➤ Press OK

custom number formatting

Result:
Finally, you will get the contact numbers in your desired customized format.

Excel phone number format

Read More: Excel Custom Number Format Multiple Conditions


Similar Readings:


Method-5: Using TEXT Function for Excel Phone Number Format

You can use the TEXT function for converting the numbers in general format into phone number format. For this purpose, we have added the Formatted Contact NO. column.

TEXT function

Steps:
➤ Type the following formula in the cell E5

=TEXT(D5,"(###) ###-####")

D5 is the contact number in a general format, “(###) ###-####” is the desired phone number format to which we want to convert and here # represents the numbers.

TEXT function

➤ Press ENTER
➤ Drag down the Fill Handle Tool

TEXT function

Result:
Afterward, you will get your desired phone number format where the first three digits in brackets represent the area code.

Excel phone number format

Note
The formatted phone numbers will be in text format here.

Read More: Excel Number Stored As Text [4 Fixes]


Method-6: Using Ampersand Operator for Excel Phone Number Format

You can use the Ampersand operator for changing the numbers of the Contact Number column into phone number format easily. Here, we will also use the LEFT function, the RIGHT function, and the MID function.

Ampersand Operator

Steps:
➤ Type the following formula in the cell E5

="("&LEFT(D5,3)&")"&" "&MID(D5,4,3)&"-"& RIGHT(D5,4)

D5 is the contact number in general format

  • LEFT(D5,3) → extracts the first 3 digits of the contact number
    Output → 808
  • MID(D5,4,3) → extracts the 3 digits of the contact number starting from the number 4 digit
    Output → 124
  • RIGHT(D5,4) → extracts the last 4 digits of the contact number
    Output → 5876
  • “(“&LEFT(D5,3)&”)”&” “&MID(D5,4,3)&”-“& RIGHT(D5,4) becomes
    “(“&808&”)”&” “&124&”-“& 5876 → & will join the brackets, numbers, space and hyphen serially
    Output → (808) 124-5876

Ampersand Operator

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Ampersand Operator

Result:
Then, you will get your desired phone number format where the first three digits in brackets represent the area code.

Excel phone number format

Note
The formatted phone numbers will be in text format here.

Related Content: How to Add Currency Symbol in Excel (6 Ways)


Method-7: Using CONCATENATE Function to Get Phone Number Format in Excel

Here, we will use the CONCATENATE function, the LEFT function, the RIGHT function, and the MID function for converting the contact numbers in general format into phone number format.

CONCATENATE function

Steps:
➤ Type the following formula in the cell E5

=CONCATENATE("(",LEFT(D5,3),")"," ",MID(D5,4,3),"-",RIGHT(D5,4))

D5 is the contact number in general format

  • LEFT(D5,3) → extracts the first 3 digits of the contact number
    Output → 808
  • MID(D5,4,3) → extracts the 3 digits of the contact number starting from the number 4 digit
    Output → 124
  • RIGHT(D5,4) → extracts the last 4 digits of the contact number
    Output → 5876
  • CONCATENATE(“(“,LEFT(D5,3),”)”,” “,MID(D5,4,3),”-“,RIGHT(D5,4)) becomes
    CONCATENATE(“(“,808,”)”,” “,124,”-“, 5876) → CONCATENATE will join the brackets, numbers, space and hyphen serially
    Output → (808) 124-5876

CONCATENATE function

➤ Press ENTER
➤ Drag down the Fill Handle Tool

CONCATENATE function

Result:
Finally, you will have your desired formatted phone numbers.

Excel phone number format

Note
The formatted phone numbers will be in text format here.

Related Content: How to Add Leading Zeros in Excel (4 Quick Methods)


Method-8: Using SUBSTITUTE Function to Clear Unnecessary Characters before Formatting

We have some imported contact numbers (we have changed the Contact Number column into the Imported Contact Number for this method) where we have some impertinent characters between the numbers. So, firstly we have to clear these characters using the SUBSTITUTE function, and then we will convert them into phone number format.

SUBSTITUTE function

Steps:
➤ Type the following formula in the cell E5

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D5,"(",""),")",""),"-","")," ",""),".",""),",","")+0

D5 is the contact number in general format.

Here, we have used 6 SUBSTITUTE functions in a loop to replace the unnecessary characters like dots, opening bracket, closing bracket, a hyphen, comma, space from the imported contact numbers with a blank.

Finally, 0 is added to convert the text (which we have got after the operation of the final SUBSTITUTE function) into value.

SUBSTITUTE function

➤ Press ENTER
➤ Drag down the Fill Handle Tool

SUBSTITUTE function

In this way, you will get the clear contact numbers and now we will follow Method-1 to convert them from general formats to phone number formats.

SUBSTITUTE function

Result:
Afterward, you will have your desired formatted phone numbers.

Excel phone number format

Related Content: How to Remove Currency Symbol in Excel (6 Ways)


Things to Notice

🔺 Before starting with the formatting procedure make sure to select the data

🔺 To avoid wrong phone number formats, at first we have to be conscious about the length and structure of the phone number formats for different countries.


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

practice


Conclusion

In this article, we tried to cover some of the ways of using phone number format in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo