Dashes in Excel can sometimes be a nuisance. Whether they’re separating phone numbers, product codes, or creating unwanted categories, there are times when you need your data in a cleaner, raw form. Manually deleting each dash is tedious, but Excel offers several efficient solutions.
Here are three ways to remove dashes from your data, whether it’s a single cell or an entire dataset.
Method #1 – Using SUBSTITUTE Function
The SUBSTITUTE function is the most effective method for removing dashes because it offers the greatest flexibility. It allows you to remove all dashes or only specific instances.
The SUBSTITUTE function replaces part of a text string with new text. In this case, we’ll replace dashes with an empty text string, effectively removing them.
Here’s the basic formula:
=SUBSTITUTE(cell_reference,"-","")
Where cell_reference is the cell containing the data you want to modify.
Step 1: Select the cell where you want the result to appear.
Step 2: Enter the SUBSTITUTE formula. For example, if your data is in cell C4, the formula would be:
=SUBSTITUTE(C4,"-","")
Step 3: Press Enter. The cell will now display the data from C4 without any dashes.
Step 4: Drag the fill handle (the small square at the bottom-right of the cell) down to apply the formula to other cells in the column.
This approach treats the output as text, preserving any leading zeros.
Converting to Number Format
If you need the result to be a number, you can wrap the SUBSTITUTE function within the VALUE function:
=VALUE(SUBSTITUTE(C4,"-",""))
This converts the text output of SUBSTITUTE into a numerical value. Be aware that this will remove any leading zeros.
Removing Specific Dashes
To remove only a specific instance of a dash, use the fourth argument in the SUBSTITUTE function to indicate the occurrence number. For example, to remove only the second dash:
=SUBSTITUTE(C4,"-","",2)
Method #2 – Using Find and Replace Feature
The Find and Replace feature is a quick way to remove all dashes from a selected range of cells or an entire sheet.
Step 1: Select the cells containing the dashes you want to remove. If you want to apply it to the entire sheet, skip this step.
Step 2: Press Ctrl + H to open the Find and Replace dialog box. Alternatively, go to the Home tab, click Find & Select in the Editing group, and choose Replace.
Step 3: In the Find what field, enter a hyphen (-).
Step 4: Leave the Replace with field empty.
Step 5: Click Replace All.
Step 6: Excel will display a message indicating the number of replacements made. Click OK and close the dialog box.
The dashes will be removed from the selected cells. However, keep in mind that if the resulting value is a number with a leading zero, Excel will remove the leading zero because it automatically converts the values to number.
- This method removes all dashes in a cell.
- You can’t choose to remove only the first or second dash, for example.
- For alphabetic text, you might need to replace the hyphen with a space instead of blank text (e.g., “Holly-Garfield” becomes “Holly Garfield”).
Method #3 – Using VBA
VBA (Visual Basic for Applications) provides a way to automate tasks in Excel, including removing dashes. This method is useful if you need to perform this operation frequently.
Step 1: Open the VBA editor by pressing Alt + F11.
Step 2: In the VBA editor, go to Insert > Module.
Step 3: Paste the following code into the Module 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
Step 4: Run the code by pressing F5 or clicking the Run button in the toolbar.
Step 5: In the pop-up window, enter the target range or select the range on the sheet and click OK.
This code first formats the selected cells as text to preserve leading zeros and then uses the Replace function to remove all dashes.
Step 6: Close the Visual Basic editor to return to the worksheet.
Any error notifications can be removed by selecting the cells showing the error, clicking the error icon and selecting the Ignore Error option.
These methods offer different approaches to removing dashes in Excel. The SUBSTITUTE function is generally the most versatile, while Find and Replace is the quickest for simple replacements and VBA is best for automating complex or repetitive tasks.