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

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.


Download Practice Workbook

Download this practice workbook from the link below.

.


2 Ways to Format Phone Number with Dashes in Excel

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 now become formatted with dashes.

Read More: How to Add Dashes to SSN in Excel (6 Methods)


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

Read More: Excel Formula to Change Phone Number Format (5 Examples)


1.3 Using LEFT, MID, and RIGHT Functions

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

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 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: [Solved!]: Excel Phone Number Format Not Working (4 Solutions)


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 (Every Possible Way)


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 here 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 (5 Methods)


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 to use 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. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.


Related Articles

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

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo