Editor choice

How to Remove Dashes from Phone Number in Excel

Get FREE Advanced Excel Exercises with Solutions!

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 IdName, and Phone Number columns.

Remove Dashes from Phone Number in Excel


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. 

Apply Find & Select Feature to Remove Dashes from Phone Numbers

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

  • Thus, all the Dashes/Hyphen gets automatically replaced as shown in the image below

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

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.

Use Format Cell Feature to Remove Dashes from Phone Numbers

  • Now, click on More Number Formats…

  • Pick 00000000000 type from Custom and click on OK.

Use Format Cell Feature to Remove Dashes from Phone Numbers

  • 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,"-","")

Apply SUBSTITUTE Function to Remove Dashes from Phone Numbers


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.

Use VBA Macro to Remove Dashes from Phone Numbers

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

Use VBA Macro to Remove Dashes from Phone Numbers

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.


Further Readings

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo