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