How to Remove Dashes in Excel

There are a few forms of data where you could have dashes in between. For example, an SNN number, dates, and mobile numbers. Now, in Excel, there are a few ways with formulas that you can use to remove these dashes from those values.

In this tutorial, we will look at different methods that we can use to remove dashes from values in Excel.

Remove Dashes from an SSN (Social Security Number)

1. First, in cell B2, enter “SUBSTITUTE(“.
2. After that, in the first argument, refer to the cell where you have the SSN.
3. Next, enter a dash using double quotation marks.
4. Now, for the third argument, enter a blank value using double quotation marks.
5. In the end, enter the closing parentheses and hit enter to get the result.

=SUBSTITUTE(A1,"-","")

Once you enter the formula, you’ll get a value without dashes in the cell

But I’d suggest you apply the SSN format whenever you are entering the SSN in Excel. This will not add dashes to the value but it is a format with dashes.

Format Cell Options ⇢ Numbers ⇢ Special ⇢ Social Security Number.

Use Find and Replace to Remove Dashes in Excel

1. First, select the range of cells from where you want to remove the dashes.
2. Next, open the find and replace option by using the keyboard shortcut Ctrl + H.
3. After that, enter a dash in the Find what.
4. Now, in the replace with, don’t enter anything.
5. In the end, click on Replace All.

The moment you click on the “Replace All” button, it will replace dashes from all the selected cells. And if you don’t select any cell, it will remove dashes from the entire worksheet.

Click on the Options button and select the workbook from the “Within” drop-down to remove dashed from the entire workbook.

Use VBA Codes to Remove all the Dashes

You can also run a VBA code to remove dashes from the selected cells. Below you have the code that you can use.

Sub remove_dashes()
Dim iCell As Range
For Each iCell In Selection.Cells
    iCell.Value = Replace(iCell, "-", "")
Next iCell
End Sub

To use this code: select the range of cells or a single cell and run the code.

But if you want to remove the dashes and want to apply the format of the social security number to the selected cells, you can use the below code instead of the above.

Sub remove_dashes()
Dim iCell As Range
For Each iCell In Selection.Cells
    iCell.Value = Replace(iCell, "-", "")
    iCell.NumberFormat = "000-00-0000"
Next iCell
End Sub

It will remove the dashes from the number but also apply SSN format to the values, as you can see in the following example.