Let’s take an example: You work in human resources, and you organize employee records. You receive a workbook with social security numbers (SSN) that have dashes, like “123-45-6789”, but your system requires the numbers without dashes, “123456789”, for using it further.
In Excel, there are a few ways with formulas that you can use to remove these dashes from these SSNs. This tutorial will all the methods for removing dashes from a SSN in Excel.
Remove Dashes from an SSN (Social Security Number)
- First, in cell B2, enter “SUBSTITUTE(“.
- After that, in the first argument, refer to the cell where you have the SSN.
- Next, enter a dash using double quotation marks.
- For the third argument, enter a blank value using double quotation marks.
- 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:
(SUBSTITUTE replaces specific text within a cell with new text. You tell it the text you want to replace and what to replace it with, and it changes all instances in the cell.)
- 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.
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:
- Open the Find and Replace: Once your workbook 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. Or, you can go to the “Home” tab, and select “Find & Select”. From the drop-down list, select ‘Replace.’
- 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.
- 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.
- 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.
Create Custom Function for Removing Dashes
Like the code I have shared above, you can also use a code to create a custom function specifically designed to remove dashes from an SSN, and in this custom function, you don’t need to define anything separately. You don’t need to write a complex formula.
You can refer to the cell where you have the dashes, which will remove them and return the result in the cell.
Function RemoveChar(rng As String, chr As String) As String
RemoveChar = Replace(rng, chr, "")
End Function Copy and paste this code into the Visual Basic editor to use this function. Once you do that, return to your worksheet and enter this function referring to the cell where you have the SSN.
Also, specify the dash in the function’s second argument and hit enter to get the result.
=RemoveChar(A1,"-")
Array Formula with REPLACE and SEARCH
You can use an array formula combining REPLACE and SEARCH to remove all dashes. You can use an array formula combining REPLACE and SEARCH to remove all dashes. You can enter this formula as an array formula with Ctrl+Shift+Enter (in older Excel versions) or directly in newer versions that support dynamic arrays.
=TEXTJOIN("", TRUE, IF(MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1)="-", "", MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1))) It works by checking each character in cell A1: if the character is a dash, it skips it; if not, it keeps it. It then combines all the characters in the text into a single text without dashes.
Use Custom Formatting for SSN
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:
- Select the cell or range of cells that you want to format.
- Right-click the selected cells and choose ‘Format Cells’ from the context menu.
- In the “Format Cells” dialog box, click on the “Special” tab, then select “Social Security Number”.
- Click ‘OK’ to apply the formatting.