How to Remove Dashes (or Hyphens) in Excel

If you have data in excel that contains dashes or hyphens and you need just the numbers.

If you have a handful of dashes, you can just do it manually and call it a day but if you have to deal with a big set of data this is certainly not an option. So, today I’ll show you two methods how to remove dashes or hyphens in Excel and keep the values intact.

Of the top of my head, this comes particularly handy if you have to deal with a list of SSNs in excel and you need to remove all dashes and get just the number use the second method which uses an Excel formula to achieve that.

Use Find and Replace to remove dashes

Using find and replace to remove hyphens and dashes in Excel is the fastest way to get just the values. It’s my favorite method to convert a string of numbers in a column with digits separated by dashes to raw numbers. However, it does have its limitations. For example, you can’t use it if you have leading zeros which you want to preserve.

Here’s how to use find and replace to remove dashes in Excel:

1. Select the range of cells that you want to remove dashes or hyphens from
2. Press Ctrl+H to get the Find and replace dialog box (another way to do this without using shortcuts is go to HOME, then Find and search and press Replace)
3. In “Find what” field the dialog box type a hyphen
4. Leave “Replace with” field blank
5. Click “Replace all”

NOTE:

1. DO NOT use Find and replace if your original data contains leading zeros. Excel automatically removes the leading zeros in numbers and leaves just the number. Not sure? Try entering ‘007’ in a cell and excel will automatically strip the placehoder zeros and you will have just ‘7’ in the cell.
2. This method changes the original data. Make a copy of the file if you need to keep the original data as well.

Remove hyphens using a FORMULA

Another simple way you can use to remove dashes in your data is to use the SUBSTITUTE formula.

It’s the best way to convert SSNs to numbers with no dashes.

Social Security numbers are 9-digit numbers in the following format “AAA-GG-SSSS“. SSNs have 9 digits separated by 2 dashes. If you want to get rid of the dashes and just get the SSNs in the format “AAAGGSSSS” use this formula:

=SUBSTITUTE(A2,”-“,””)

The SUBSTITUTE formula has three arguments:

1. A2 – this is the cell containing the original data with dashes that you want to change
2. “-“ – this is the string you want to remove, a dash in this example
3. “” – what you want to replace the string with. In our example we want to replace the dash with blank (null)

So, in the SUBSTITUTE formula in our example will take the data in A2 and replace all dashes with a blank, which means that it will strip all hyphens or dashes from the cell.

If you have a column with data to strip the dashes from, enter the substitute formula for the first cell in the range. Change A2 to the first value from your column.

Then move your mouse until the pointer turns to a plus sign, press your left mouse button and drag the formula down as long as needed.

That’s it.

Now you know 2 super simple ways how to remove dashes in Excel (e.g in social security numbers). Which method do you prefer and why? Do you know another easy method that we haven’t mentioned? Either way, drop a comment below and let me know.