How to Remove Dashes from Phone Number in Excel

Method 1 – Applying Find and Replace Feature

• Select the range of cells where the phone numbers are located.
• Go to the Home tab > Find & Select dropdown > Replace.
  
  • The Find and Replace dialog box will open.
    Note: You can press CTRL + H to bring out the Find and Replace dialog box.
  • In the Find and Replace dialog box:
  • In the Find What field, type Dash/Hyphen (-)
  • Leave the Replace With field Empty / Null ( )
  • Click on the Replace All.
• A message box will be displayed, informing users of the number of replacements made.

All the dashes will be removed from the phone numbers.

Read More: How to Remove Dashes from SSN in Excel

Method 2 – Using Format Cell Feature

• Select the range of cells from where you want to remove the dashes.
• Go to the Home tab > Number group > Dialog Box Launcher Icon.
  The Format Cells dialog box will pop up.
  Note: You can use the Ctrl+1 shortcut key to open the Format Cells dialog box.
• In the Format Cells dialog box:
  • Go to the Number tab > Custom Category.
  • Pick 00000000000 from the available types under the Type.
  • Click on OK.
    

The dashes will be removed from the phone numbers.

Read More: How to Remove Non-Alphanumeric Characters in Excel

Method 3 – Applying SUBSTITUTE Function

If you want to keep the original phone numbers with the dashes but remove the dashes and place them elsewhere, you can use the SUBSTITUTE function.

• Select a blank cell and enter the following formula:=SUBSTITUTE(D5,"-","")
  D5 is the cell containing a phone number with dashes.
• Press ENTER.
  The result will be as shown below.
  
• Use the Fill Handle tool to autofill the remaining cells.
  

The above formula eliminates all dashes from phone numbers.

Note: If you only want to remove a specific instance of dash, you can use the following formula: =SUBSTITUTE(D5,"-","", N)
Replace N with the instance number of the dash you want to remove. For example, to remove the 2nd dash, use the formula: =SUBSTITUTE(D5,"-","",2)

Method 4 – Using Flash Fill Feature

If you’re using Excel 2013 or a newer version, you can utilize the Flash Fill feature to remove dashes from phone numbers more efficiently compared to using the SUBSTITUTE function. However, it’s important to note that the formatting of the phone numbers must be uniform for this method to work correctly. In other words, the position of the dashes must always be the same for all phone numbers.

• Click on a cell adjacent to the first cell of the column containing the phone numbers.

Manually enter the first phone number without the dashes.

• Select the cell E5 and press Ctrl+E to flash-fill the rest of the cells.
  Note: To launch the Flash Fill feature, you can also go to the Home tab > Editing group > Fill drop-down > Flash Fill.

The remaining phone numbers will Flash Fill without the dashes.

Method 5 – Running VBA Macro

If you need to remove dashes from a range of cells frequently, using a VBA macro is the most efficient way.

• Press ALT+F11 to open Microsoft Visual Basic.
• Click on Module from the Insert tab.Enter the following code in the module window:

• Sub DeleteDashes()
  Dim rng As Range
  Dim WorkRng As Range
  On Error Resume Next
  Set WorkRng = Application.Selection
  Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
  Application.ScreenUpdating = False
  For Each rng In WorkRng
  rng.NumberFormat = "@"
  rng.Value = VBA.Replace(rng.Value, "-", "")
  Next
  Application.ScreenUpdating = True
  End Sub

• Click on Run to run the code. Alternatively, you can press F5.An InputBox will pop up.
• In the InputBox :
  • Select the range of cells from which you want to remove the dashes.
  • Click on OK.

  

The dashes will be removed from the phone numbers.

If you need to run the code frequently, you can assign a shortcut key to the macro to run it directly from the worksheet.

Method 6 – Using Power Query

If you need to import phone numbers into your working worksheet from an external source or another worksheet using Power Query, you can format them and remove any unnecessary dashes directly in the Power Query window before importing them.

• To import data, go to the Data tab > Get & Transform Data group > From Table/Range.
  An InputBox named Create Table will pop up.
  Note: As we want to import the data from a range into the worksheet, we will choose the From Table/Range option. However, you need to select an option according to your desired source type.
• In the Create Table InputBox, select the data source range and click OK.

• In the Power Query window:
  • Select the column containing phone numbers with dashes.
  • Right-click on it to open the context menu
  • Click on Replace Values in the context menu.
    
  • In the Replace Values window:
    • Type the dash symbol (-) in the Value to Find field.
    • Leave the Replace With field empty.
    • Click OK.

    The dashes will be removed from the phone numbers.
    

• Click the Close & Load option to load the phone numbers without dashes in a new worksheet.
  

The phone numbers without dashes will be stored in a new worksheet as shown below.

Download Practice Workbook

Frequently Asked Question

How do I remove special characters from a phone number in Excel?

To remove a special character from a phone number in Excel, follow the steps below:

1. Select the range of cells containing phone numbers with special characters.
2. Press Ctrl + H to open the Find and Replace dialog.
3. In the Find What box, type the character.
4. Leave the Replace With box blank.
5. Click Replace All.