Maruf Islam
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 will explain 4 suitable ways to remove dashes from phone number in Excel. I hope it will be very helpful for you if you are looking for an efficient way to do so.
Download Dataset
4 Suitable Ways to Remove Dashes from Phone Number in Excel
In order to remove dashes from phone number with ease, I will try to apply the Find & Select feature, Format Cell feature, SUBSTITUTE Function, and VBA. For more simplification, I have a dataset with a list of customers’ Phone Numbers. I have arranged the data in the Customer Id, Name, and Phone Number columns.
1. Apply Find & Select Feature to Remove Dashes from Phone Number
In order to remove dashes from phone numbers, we can simply apply Replace from the Find & Select Feature. Let’s see the details in the following section.
Steps:
- Go to the Home Tab first.
- Next, click Find & Select from the Editing section and select Replace.Â
-  In the Find and Replace dialog Box, type Dash/Hyphen (-) in the Find What box and press Null ( ) in the Replace With box
- After that, click on Replace All.
A confirmation window will pop up. Click OK to finish the process.
A confirmation window will pop up. Click OK to finish the process.
- Thus, all the Dashes/Hyphen gets automatically replaced as shown in the image below
Read more: How to Remove Non-numeric Characters from Cells in Excel
2. Use Format Cell Feature to Remove Dashes from Phone Number
We can also use the Format Cell feature to remove dashes from phone numbers in Excel. The explanation is given below.
Steps:
- First of all, select the range of cells, you want to remove the dashes.
- Go to the Home tab next.
- Click on the extended key in Number Format from the ribbon.
- Now, click on More Number Formats…
- Pick 00000000000 type from Custom and click on OK.
- We will have phone numbers without dashes.
Read more: How to Remove Special Characters in Excel
3. Apply SUBSTITUTE Function to Remove Dashes from Phone Number
Another smart way to remove dashes & show phone numbers in another cell is the application of the SUBSTITUTE Function.
Steps:
- Apply the following formula in your preferred cell (i.e. E5) and press ENTER to remove dashes from phone numbers.
=SUBSTITUTE(D5,"-","")
- Drag the Fill Handle up to AutoFill the rest cells in Column E.
4. Use VBA Macro to Remove Dashes from Phone Number
A VBA macro code removes dashes from a selected range of cells via a code run by Microsoft Visual Basic. It is the smartest way among all to remove dashes.
Steps:
- Press ALT+F11  altogether to open Microsoft Visual Basic.
- Next, click on Module from the Insert tab.
- Write 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
- Now, click on Run to run the code. Alternatively, you can press F5 .
- Â A Selection window will pop up. Select a range of cells from where you want to remove the dashes.
- Click on OK to apply the VBA.
- This is how we can smartly have the dashes removed.
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 function, and VBA Macro 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.
