How to Remove Dashes in Excel

There are a few forms of data where you could have dashes in between, such as 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.

This tutorial will explain different methods for removing 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. For the third argument, enter a blank value using double quotation marks.
5. Ultimately, enter the closing parentheses and hit enter to get the result.

The following formula is what we have written:

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

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

In this formula:

• A1: This cell reference indicates where Excel should look for the text to replace. In this example, Excel would look in cell A1. However, you could replace A1 with any cell reference, depending on where your data is located.
• “-“: This is the text that Excel will search for in the cell specified by the reference. In this case, it is looking for the dash character, but it could be any text, depending on what you want to replace.
• “”: This is the text that will replace what Excel finds. In this case, it’s an empty text string, which means Excel will remove the dash and not replace it with anything. If you wanted to replace the dash with another character, you would put that character or string of characters inside the quotation marks.

So, when you use the formula =SUBSTITUTE(A1,”-,””), Excel goes to cell A1, looks for any instances of a dash (“-“), and then removes it.

However, I suggest you apply the SSN format when entering the SSN in Excel. This will not add dashes to the value, but it is a format with dashes.

To apply the Social Security Number (SSN) format in Excel, follow these steps:

1. Select the cell or range of cells that you want to format.
2. Right-click the selected cells and choose ‘Format Cells’ from the context menu.
3. In the ‘Format Cells’ dialog box, click on the ‘Number’ tab, then select ‘Custom’ from the category list.
4. In the ‘Type’ input field, type the following custom format: 000-00-0000
5. Click ‘OK’ to close the dialog box.

Use Find and Replace to Remove Dashes in Excel

Here’s detailed steps on how to use the Find and Replace function to remove dashes in Excel:

1. Open the Find and Replace Dialog Box: Once your spreadsheet is open, you can bring up the Find and Replace dialog box by pressing Ctrl + H on your keyboard. This shortcut works in all versions of Excel. Alternatively, you can go to the ‘Home’ tab in the ribbon at the top of Excel, and in the ‘Editing’ group, select ‘Find & Select.’ From the drop-down list, select ‘Replace.’
2. Enter the Dash in the ‘Find What’: Box: In the Find and Replace dialog box, you’ll see two main input fields: ‘Find what:’ and ‘Replace with:’. In the ‘Find what:’ box, type the dash (-). This tells Excel that you’re looking for all instances of the dash character in your spreadsheet.
3. Leave the ‘Replace With:’ Box Empty: Don’t type anything in the ‘Replace with:’ box. Leaving this box empty tells Excel that you want to replace the dashes it finds with nothing—in other words, you want to remove them.
4. Replace All Dashes: Finally, click the ‘Replace All’ button. Excel will then search through your entire spreadsheet, find all instances of the dash, and replace them with nothing. You’ll see a dialog box that tells you how many replacements it made. Click ‘OK’ to close this box.

When you click the “Replace All” button, dashes from all the selected cells will be replaced.

If you don’t select any cell, dashes will be removed from the worksheet.

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

Note: If you want to remove dashes from an entire column or row, make sure to select that column or row.

Use VBA Codes to Remove all the Dashes

You can also run VBA code to remove dashes from the selected cells. The code is below.

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 all the cells in a range or a single cell and run the code.

How this code works

• Dim iCell As Range: This line declares a variable iCell as an object of type Range. Range is a type that represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3D range.
• For Each iCell In Selection.Cells: This line starts a loop that goes through each cell in the selection. The Selection object represents the currently selected cells in the active window.
• iCell.Value = Replace(iCell, “-“, “”): This line uses the Replace function to replace any dashes (“-“) in the current cell (iCell) with nothing (“”), effectively removing all dashes from the cell. The .Value property sets or returns the value of the cell.
• Next iCell: This line ends the loop. After the code inside the loop has been executed for the current cell, it moves on to the next cell in the selection until all selected cells have been processed.

But if you want to remove the dashes and apply the social security number format to the selected cells, you can use the code below 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 and apply the SSN format to the values, as shown in the following example.