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.

How to Use Phone Number Format in Excel: 8 Examples

Here, we have used the following table to demonstrate 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 version according to your convenience.

1. Use Built-In Phone Number Format of Excel

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.

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

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

Category → Special
Type → Phone Number

➤ Press OK

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

Read More: Excel Phone Number Format Not Working

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.

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

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

Category → Special
Type → Zip Code + 4

➤ Press OK

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

Read More: Keep 0 Before a Phone Number in Excel

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.

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

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

Category → Special
Type → Social Security Number

➤ Press OK

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

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.

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

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

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

Read More: Format Phone Number with Country Code

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.

Steps:
➤ Type the following formula in the cell E5

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.

➤ Press ENTER
➤ Drag down the Fill Handle Tool

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

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

Read More: Excel Formula to Change Phone Number Format

6. Using Ampersand Operator for Excel Phone Number Format

You can use the Ampersand operator to change 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.

Steps:
➤ Type the following formula in the cell E5

D5 is the contact number in general format

➤ Press ENTER
➤ Drag down the Fill Handle Tool

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

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

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.

Steps:
➤ Type the following formula in the cell E5

D5 is the contact number in general format

➤ Press ENTER
➤ Drag down the Fill Handle Tool

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

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

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.

Steps:
➤ Type the following formula in the cell E5

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.

➤ Press ENTER
➤ Drag down the Fill Handle Tool

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

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

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.

Download Workbook

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.

Phone Number Format in Excel: Knowledge Hub

• How to Write Phone Number in Excel
• Format Phone Number with Dashes
• Format Phone Number with Extension
• Remove Parentheses from Phone Numbers

<<< Go Back to Number Format | Learn Excel