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.

Dataset of Excel Format Phone Number with Dashes.

method

1. Separate Phone Numbers with Dashes by Formatting Cells

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.

Format Cells Option

1.1 Use Custom Formatting Option to Format Phone Numbers with Dashes

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.

Context menu list to Format cells.

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

Select the Custom option from the Format Cells box.

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

Phone numbers formatted in Excel with dashes.

Format Cells Option

1.2 Use Special Formatting Option to Insert Dashes Between Phone Number

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.

Context menu list to format phone numbers.

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

Select the Special option from the Format Cells box.

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

Formatting of Phone numbers done with dashes in Excel.

method

2. Use REPLACE Function to Format Phone Numbers with Dashes in Excel

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.

=REPLACE(REPLACE(D5,4,0,”-“),8,0,”-“)

🔨 Formula Breakdown

👉  Here, REPLACE(D5,4,0,”-“) means, insert a dash(-) at the fourth character.

👉  Secondly, REPLACE(REPLACE(D5,4,0,”-“),8,0,”-“) signals to insert a dash again at the 8th character after the 4th character.

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

REPLACE function to insert dashes to format phone numbers in Excel.

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

Phone numbers formatted in Excel including dashes.

method

3. Implement TEXT Function to Format 10 Digit Phone Number

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.

=TEXT(D5,”???-???-????”)

🔨 Formula Breakdown

👉  TEXT function commands to transform the text of D5 as 123-456-7890.

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

TEXT function to insert dashes to format phone numbers in Excel.

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

Phone numbers formatted in Excel inserting dashes.

method

4. Apply LEFT, MIDDLE, and RIGHT Functions to Insert Dashes

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.

=LEFT(D5,3)&”-“&MID(D5,4,3)&”-“&RIGHT(D5,4)

🔨 Formula Breakdown

👉  LEFT(D5,3) counts the first 3 characters.

👉  MID(D5,4,3) signals to count from the 4th character to next 3 characters.

👉  RIGHT(D5,4) dictates to count the last 4 characters.

👉  All these separated characters are joined with dashes(-).

👉  Therefore, formation remains 3-3-4; that means formula writes the phone numbers as 123-456-7890.

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

LEFT, MID, and RIGHT functions to insert dashes to format phone numbers in Excel.

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

Phone numbers formatted in Excel with dashes.

method

5. Format Number with Dashes Using VBA Macro

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.

Enabling VBA

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

Getting the Developer option in the Top Ribbon.

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

VBA Module to insert code to format phone numbers.

Code

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

VBA code to format phone numbers with dashes.

  • Lastly, we obtain the final result automatically.

Phone numbers formatted.


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.

Find and Replace feature to join text by dashes.

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

FInd and Replace box.

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

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

(Visited 26 times, 1 visits today)
MD Tanvir Rahman

MD Tanvir Rahman

Hello, I am Tanvir. Graduated from BUET. Had a year-long Experience at Operation of Vitacan Industries Ltd. I found Excel is the most unique software to reduce both time and paper. Now, I am an Excel enthusiast, love to play with data and publish content.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo