5 Tricks to Format Phone Number with Dashes in Excel

When your clients put phone numbers, do they type like, 1490205645? Are you tired of formatting them every day one by one? Irony but we can feel you. Perhaps, this article is looking for you. We all know that it is quite boring to read and navigate contact numbers Without formatting. So, to make the dataset more user-friendly, concise, and readable, dataset formatting is inevitable. Specifically, we need to format phone number with dashes or spaces in Excel. In this article, we will learn how to format phone number with dashes in Excel.

📁 Download Excel File

To practice please download the Excel file from here:

Learn to Format Phone Number with Dashes in Excel with These 5 Methods

5 easy examples to learn to insert dashes to format phone number in Excel. Here, we consider a dataset named Contact List of Hotel California containing the responsible personnel’s Name, designation as well as a phone number. Dataset has 4 columns along with 13 rows. To format phone numbers we are going to use the Format Cells Option from the Context Menu as well as REPLACE, TEXT, LEFT, MIDDLE, and RIGHT functions. Hey buddy, let’s get started.

Use of the Format Cells Option from the Context Menu list is a handy way to format phones. From the Format Cells Option, one can format phone numbers in two ways. Please stay tuned.

By applying suitable changes in the Custom option from Format Cells Option, you can format phone numbers without any hustle. Please follow the steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Primarily, select the entire column E containing phone numbers.
• Secondarily, Right-Click on the mouse to get the Context Menu list.
• Thirdly, from the Context Menu list, select the Format Cells Option.

• Thus, a box named Format Cells appears.
• Next, from the Number menu, navigate to the Custom segment.
• After that, type ###-###-#### at Type cell as our desired format and hit the OK button.

• Finally, formatted phone numbers take place in column E as 123-456-7890.

Operating from the Special option Format Cells box, you can format phone numbers within a moment. Please follow the steps.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select the data of column E containing phone numbers.
• Secondly, Right-Click on the mouse to get the Context Menu list.
• Thirdly, from the Context Menu list, select the Format Cells Option.

• Thus, a box named Format Cells appears.
• Next, from the Number menu, navigate to the Special segment.
• After that, select Phone Number at Type cell as our desired format.
• Then, As we are American citizens, select English (United States) in the Locale cell.
• Now, hit the OK button to continue the operation.

• Therefore, we obtain the formatted phone numbers as (123) 456-7890 including dashes.

The use of REPLACE function can be a key to formatting phone numbers with dashes. Please follow the necessary steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Initially select a cell i.e. E6 to insert a formula containing REPLACE function.
• Then, insert the formula in the E6 cell.

• Therefore, we achieve the result as 123-456-7890 in the E6 cell.

• Finally, by auto-filling the cells, we obtain the formatted phone number at each cell.

One can use the TEXT function to format phone numbers as well as texts with dashes. Please follow the necessary steps.

⬇️⬇️ STEPS ⬇️⬇️

• Initially select a cell i.e. E6 to insert a formula containing the TEXT function.
• Then, input the formula in the E6 cell.

• Therefore, we achieve the result as 123-456-7890 in the E6 cell applying the TEXT function.

• In the end, we obtain the final result by filling the cells automatically.

LEFT, MIDDLE, and RIGHT functions can be used to insert dashes in a cell containing texts or numbers. Please follow the steps.

⬇️⬇️ STEPS ⬇️⬇️

• Initially select a cell i.e. E6 to insert a formula containing functions.
• Then, input the formula in the E6 cell containing LEFT, MID, and RIGHT functions.

• Therefore, we get the result as 123-456-7890 in the E6 cell applying LEFT, MID, and RIGHT functions.

• Lastly, Auto-fill the cells and get the desired output.

Using VBA code we can easily format phone numbers including dashes. With the help of the Developer option, you can generate a program on your own.  Please follow the steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Check first if your Developer option is available or not.
• Secondly, to enable the Developer option, Right-Click on the mouse at the Top Ribbon and select Customize the Ribbon.

• Thirdly, click on Developer and click on the OK button.

• Now Developer mode is on.
• Then, click on the Visual Basic feature.
• Next, to insert the VBA code, click on Module from the Insert menu.

Code

Sub PhoneNumberFormat()
    Range("E5:E13").Select
    Selection.NumberFormat = "###-###-####"
End Sub

• Lastly, we obtain the final result automatically.

How to Insert Dash Between Text in Excel

The easiest way is to find space between texts in a dataset and replace them with a dash. Please follow the steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select the entire data range B5:F13.
• Then drop down the Editing option from the Home menu of the Top Ribbon.
• Next, navigate to the Find and Select option to select the Replace option.

• Thus a box named Find and Replace appears.
• Then, insert { }= Space in the Find what cell and {–}= Dash in Replace with cell.
• After that hit the Replace All button.

• Finally, we obtain the texts having a dash between texts.

📄 Important Notes

🖊️  Enable the Developer menu first to execute VBA code.

🖊️  Count the character string carefully while using LEFT, MID, and RIGHT functions.

🖊️  During the use of the Find and Replace feature insert Space in the Find what cell to find space between texts.

📝 Takeaway from This Article

📌  Format Cells Option from Context Menu to format the numbers.

📌  REPLACE function to insert dashes in the phone number.

📌  TEXT function to format texts or numbers.

📌  LEFT, MID, and RIGHT functions to count the strings and insert dashes within the character.

📌  VBA Macro to format phone numbers with dashes.

Conclusion

We demonstrated every possible way to format phone numbers with dashes in Excel. I hope you enjoyed your learning. Any suggestions, as well as queries, are appreciated. Also, leave your thought below. For better understanding and new knowledge, don’t forget to visit www.ExcelDen.com.