How to Format Phone Number with Dashes in Excel (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, we need to deal with datasets packed with random phone numbers without formatting. Using and understanding those phone numbers is difficult as every section of a phone number has a separate meaning, and to comprehend this, we need to format the phone numbers appropriately. In this article, we are going to show how we can format phone number with dashes properly in Excel easily and efficiently.

How to Format Phone Number with Dashes in Excel: 2 Ways

To demonstrate how to format the phone number with dashes, we are going to use the following spreadsheet phone number data, which are completely random phone numbers and only for practice and demonstration purposes.

1. Format Phone Number with Dashes Using Functions

We can format the phone numbers using various types of formulas like REPLACE, TEXT,and different types of string functions like LEFT, LEN, RIGHT, MID, etc. All these methods are explained with examples below.

1.1 Using Replace Function

In this method, we are using the REPLACE function to format phone numbers with dashes.

Steps

• First, select cell C5 and enter the following formula:
`=REPLACE(REPLACE(B5,4,0,"-"),8,0,"-")`

Here, cell B5 indicates the first row of the dataset. Replace cell B5 with your datasetâ€™s first row.

• After clicking the ENTER button, the destination cell will contain the formatted number with a dash.
• Select the Fill handle icon in the corner of the cell and drag it down to the end of the dataset

Then, all of the unformatted phone numbers become formatted with dashes.

1.2 Using TEXT Function

In this method, we are going to demonstrate how to format phone numbers with the help of the TEXT function.

Steps

• First, select cell C5, then copy and paste the following formula into the Formula Bar.
`=TEXT(B5,"???-???-????")`

Here, cell B5 indicates the first row of the unformatted phone number column. Edit cell B5 with your datasetâ€™s first-row number.

• Then, after pressing the ENTER button, you will see a formatted phone number.
• Select the Fill handle button in the corner of the cell and drag it down to the end of the dataset.

Then, all of the unformatted phone numbers now become formatted with dashes.

1.3 Using LEFT, MID, and RIGHT Functions

We can format the phone numbers with the help of various string functions like MID, LEFT,Â and RIGHT.

Steps

• First, select the first row in the destination column, then copy and paste the following formula into the Formula Bar.
`=LEFT(B5,3)&"-"&MID(B5,5,3)&"-"&RIGHT(B5,4)`

Here, cell B5 indicates the first row of the unformatted phone number column. Now, edit cell B5 with your datasetâ€™s first-row number.

• Next, after pressing the ENTER button, you will see a formatted phone number with a dash
• After that, select the Fill handle button in the corner of the cell and drag it down to the end of the dataset.

Then, all of the unformatted phone numbers now become formatted with dashes.

2. Using Format Cells Option to Format Phone Number with Dashes

2.1Â  Using Custom Option

In this method, the Custom formatting option will be used to format the phone numbers.

Steps

• In the beginning, copy the unformatted numbers to a new column.

• Then, select the entire column and, right-click from the context menu, select Format Cells.

• After clicking the Format Cells box, a new dialog box will open, as shown below.
• In that box, Go to Number > Custom > Type.
• In the Type box, enter your desired text format. For example, here, my target format is ###-###-####. Entering this format will alter the format of the number listed in the selected column. A sample of the changed format is shown above the Type.

• After clicking OK, you will notice that all of the phone numbers are now changed into a new format.

Read More: How to Write Phone Number in Excel

2.2 Using Special Option

In this method, we are going to use a Special option to format the phone number.

Steps

• In the beginning, copy the unformatted numbers to a new column.

• Then, select the entire column and, right-click from the context menu, select Format Cells.

Upon clicking the Format Cells box, a new dialog box will open, as shown below.

• In that box, Go to Number > Special > Phone Number.
• After selecting the Phone Number option, select the Locale or which locationâ€™s phone number format you wish to choose.

For illustration purposes, the USA location is selected.

Upon choosing the location, the phone number format will be in your desired format.

.

Conclusion

To sum it up, the question â€œhow we can format phone numbers with dashesâ€œ can be answered in two principal ways. One is by using formulas, and another one is by using a formatting tool in Excel. The format tool is most efficient and less time-consuming. But this formatting tool is not as flexible as it has fixed format saved before. On the other hand, formulas are more flexible. Users can format the number however they like. In the meantime, a workbook containing a dataset with all the methods done is attached for practice. Feel free to ask any questions or feedback through the comment section.

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF