Rubayed Razib Suprov
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.
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 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.
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.
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.
- 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 (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.
- 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.
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.
