Remove Dashes from Phone Number in Excel(4 Ways)

In datasets, we come across phone numbers containing Dashes (-). For obvious reasons, we have to remove the dashes from the phone number entries. In this article, I’ll try to explain some of the quickest methods like Find & Select, Format Cell, SUBSTITUTE Formula, and VBA Macro Code to remove the dashes with ease.

Suppose, I have a list of customer’s Phone Numbers,

datasetNote that this table contains some dummy information only for demonstrating the examples.

Download Dataset

4 Easy Methods to Remove Dashes from Phone Numbers in Excel

Method 1: Using Find & Select Method

Step 1: Go to Home Tab>> Click Find & Select (in Editing section)>> Select Replace. 

find & select method

Step 2: In the Replace Dialog Box, in Find What box type Dash/Hyphen (-) and Replace With box press the Null ( ). Click on Find All.

find all

You can use pressing CTRL+H to bring out the Find & Replace window.

Step 3: Click on Replace All.

replace all

Step 4: A confirmation window will pop up. Click OK.

ok window

All the Dashes/Hyphen gets automatically replaced as shown in the image below

final find & select

Notes: Keep in mind that the Find & Select method changes the raw data. Make sure you copy the raw data before executing this method.

Read more: How to Remove Non-numeric Characters from Cells in Excel

Method 2: Using Format Cell

Step 1: Select the range of cells, you want to remove the dashes.Go to Home Tab >> Click on Cell (section)>>Click on Format>>Select Format Cell. A window pops up.

format cell sequence

Step 2: On the left of the Format Cell window inside Categories, Select Custom>>Modify any format with eleven 0’s (as our phone number has 11 digits)

format cell custom

Step 3: Click on OK.

format cell

The result will similar to the image below

result format cell

Phone Numbers beginning with 0s also keep the 0s starting the number in this process.

Read more: How to Remove Special Characters in Excel

Method 3: Using Formula Method

You can remove dashes & show phone numbers in another cell by simply with a formula using the SUBSTITUTE Function.

=SUBSTITUTE(D4,"-","")

Step 1: Enter formula =SUBSTITUTE(D4,”-”,””) in an adjacent cell.

substitute function

Step 2: Drag the Fill Handle up to the last entries and the execution depicts results similar to the image below

substitute function

Method 4: Using VBA Macro Code

A VBA macro code removes dashes from a selected range of cells via a code run by Microsoft Visual Basic.

Step 1: Press ALT+F11 altogether to open Microsoft Visual Basic.

Step 2: In the Microsoft Visual Basic Toolbar, Click on Insert>> Module.

MVS interface

Step 3: Paste the following code in the Microsoft Visual Basic Module.

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

VBA code

Step 4: Press F5 to run the code. A Selection window will pop up.

Step 5: Select a range of cells you want to remove the dashes.

range selection

Step 6: Click OK. Execution of the steps produces a result similar to the image below

Final result of VBA method

If 0s are present at the beginning of the phone numbers, this method keeps them as it is.

Read more: VBA to Remove Characters from String in Excel

Conclusion

Excel datasets bear various cell formats, phone numbers are also one of them. The dataset containing phone numbers often needs to be in general format cell and dashes removed to work with. We’ve demonstrated the four easiest methods such as Find & Select, Format Cell, SUBSTITUTE Formula, and VBA Macro Code to execute the removal of dashes in any range of cells. Hope these methods do justice to your queries and help you to understand the process.


Further Readings:

Maruf Islam

I, Maruf Islam, an engineer, content writer. I completed my BSc from Bangladesh University Of Engineering & Technology, want to pursue a career in content writing & development.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo