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.
How to Remove Dashes in Excel: 3 Methods
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 take 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.
Notes: There is a major drawback in using the 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: How to Remove Dashes from SSN in Excel
2. Formula to Erase Dashes in Excel
Unlike the 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,
=SUBSTITUTE(cell, “old_string”, “new_string”)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 the dash (-) (in our case, the cell number was C5).
- Then put a comma (,) symbol and after that, write a 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,
=SUBSTITUTE(C5,”-”,””)- Press Enter.
It will replace dashes (-) (or any other text that you selected) with a null string (or the string that you replace it with).
Step 2: Drag the row down using the 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 Dashes from Phone Number 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 get 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 SubYour 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,
R.Value = VBA.Replace(R.Value, "-", "")Write it as,
R.Value = VBA.Replace(R.Value, "old_text", "new_text")Here,
old_text = the string you want to remove.
new_text = the string that you want to replace with.
Read More: How to Remove Non-Alphanumeric Characters in Excel
Download Practice Workbook
You can download the free practice Excel template from here and practice on your own.
Conclusion
This article discusses the methods of removing dashes in Excel by utilizing the Find & Replace command for beginners in Excel, by using the safest method of SUBSTITUTE formula for advanced users of Excel, and by implementing VBA code for Excel experts. I hope this article has been very beneficial to you. Feel free to ask any questions you have regarding the topic.
