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.

Format Phone Number With Dashes in Excel


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

Format Phone Number With Dashes in Excel

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.

Format Phone Number With Dashes in Excel

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.

Format Phone Number With Dashes in Excel

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

Read More: Excel Formula to Change Phone Number Format


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.

Format Phone Number With Dashes in Excel

  • 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.

Format Phone Number With Dashes in Excel

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.

Format Phone Number With Dashes in Excel

  • 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.

Format Phone Number With Dashes in Excel

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

Read More: How to Remove Parentheses from Phone Numbers in Excel


Download Practice Workbook

Download this practice workbook from the link below.

.


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.


Related Articles

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
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo