How to Remove Dashes in Excel (3 Methods)

Sometimes, we want to check certain values without dashes to have a deeper understanding of the information. But when the data are huge, it is not a wise way to remove dashes manually. In this article, you will learn how to remove dashes in Excel in the three most efficient ways.

Download Practice Template

You can download the free practice Excel template from here and practice on your own.

3 Easy Methods to Remove Dashes in Excel

This section will discuss the methods of removing dashes in Excel by utilizing the Find & Replace command in Excel, by using the SUBSTITUTE function, and by implementing VBA code.

1. Find & Replace Command to Delete Dashes

The Find & Replace command is the easiest and the most common feature to do most of the Excel-related tasks. Here we will get to know how to delete dashes by using the Find & Replace feature in Excel.

The steps to do that are given below,

Step 1:

• Select the dataset.
• Under the Home tab, go to Find & Select -> Replace.

Step 2:

• From the pop-up Find and Replace box, in the Find what field, write the dash (-) symbol.
• Leave the Replace with field blank.
• Press Replace All.

This will erase all the dashes from your dataset in Excel.

Thing You Should Keep in Mind

There is a major drawback in using Find & Replace command to remove dashes in Excel. When your data starts with number zero (0) (for example, 002-10-2324), it will remove all the leading zeros and give you an output of modified data (for example, 002-10-2324 will become 2102324). So if you want to use the Find & Replace command to delete dashes, make sure that you have a backup copy of the original data.

Read more: Remove Dashes from Phone Number in Excel

2. Formula to Erase Dashes in Excel

Unlike Find & Replace command feature in Excel, using formula is the safest and the most controlled way to extract any kind of results in Excel. To get the output of a dataset without dashes in Excel, you can implement the SUBSTITUTE function.

Generic SUBSTITUTE Formula,

Here,
old_text = the string you want to remove.
new_text = the string that you want to replace with.

Steps to remove dashes in Excel with the SUBSTITUTE function are given below,

Step 1:

• In an empty cell where you want your result to appear, first put an equal (=) sign and then write SUBSTITUTE along with it.
• Inside the brackets of the SUBSTITUTE function, first write the cell reference number from which you want to remove dash (-) (in our case, the cell number was C5).
• Then put a comma (,) symbol and after that, write dash (-) symbol inside double quotes (or any old text that you want to remove).
• Again put a comma (,) and lastly, leave blank double quotes if you want a null string instead of a dash (-) (or any new string that you want your old text to replace with).

So, our required formula will look like the following,

• Press Enter.

It will replace dashes (-) (or any other text that you selected) with null string (or the string that you replace it with).

Step 2: Drag the row down using Fill Handle to apply the formula to the rest of the dataset.

Now you have found the result of a dataset without any dashes (-).

Read more: How to Remove Special Characters in Excel

3. Embed VBA Code to Remove Dashes

If you are an experienced Excel user, then this method is only for you. Using VBA to remove dashes is the quickest and the absolute way to do the job done.

Step 1: Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

Step 2: In the pop-up code window, from the menu bar, click Insert -> Module.

Step 3: Copy the following code and paste it into the code window.

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

Your code is now ready to run.

Step 4: Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

Step 5: From the pop-up Macro window, select the Macro Name RemoveDashes -> Run.

Step 6: From the pop-up dialogue box, switch to the worksheet of interest, select the desired range and click OK.

This will replace all the dashes (-) in your dataset with a null string.

If you want to replace any other text with something else with VBA code, then just modify line no. 11 of the code according to your need.

To understand more,

Instead of writing the line as,

Write it as,

Here,

old_text = the string you want to remove.

new_text = the string that you want to replace with.

Read more: How to Remove Spaces in Excel

Conclusion

This article discusses the methods of removing dashes in Excel by utilizing the Find & Replace command for the beginners in Excel, by using the safest method of SUBSTITUTE formula for the advanced user of Excel and the by implementing VBA code for Excel experts. I hope this article has been very beneficial to you. Feel free to ask any questions if you have regarding the topic.

You May Also Like to Explore

• Delete Blank Columns in Excel (3 Ways)
• How to Remove Blank Characters in Excel (5 Methods)
• Remove Text After Character in Excel (3 Ways)
• Remove Last Character from String in Excel with VBA (2 Easy Ways)
• VBA to Remove Characters from String in Excel (7 Methods)