When working with data in excel (numbers or text), there may be times where the data has dashes or hyphens that need to be removed.
For example, say you have a large group of social security numbers in the following format: 123-456-789.
You need to remove all of the “-” symbols and just keep the numbers.
If you are thinking about going into the spreadsheet and removing each dash one by one, think again.
Removing dashes and hyphens one by one is simply not an option when you have large amounts of data.
It is a tedious and time consuming task.
Worry not, there are a couple of other methods that are simple and effective in removing dashes and/or hyphens from your data so that you are only left with numbers or text.
Let’s look at two simple ways to remove dashes or hyphens in Excel.
Removing Dashes or Hyphens in Excel using the Find and Replace feature
Excel already has a neat little feature called “Find and Replace.”
Just as the name suggests, this feature allows you to find all of the cells that have a dash or hyphen in them, and then replace it with either a space character or a null (blank).
Let’s look at an example using SNN numbers. This can also be done with phone numbers or any other type of data:
Using the above SSN data set, we are going to use the Find and Replace feature to remove all of the dashes in Column A:
- Select the dataset where you want the dashes to be removed (in this example we are selecting A(1-6)
- While holding down the Control Key, press the H key. This should have opened the Find and Replace feature dialog box.
- In the field “Find What”, you want to type in the dash symbol (-).
- The “Replace with” field remains empty.
- Next, select Replace All in the bottom left corner.
This method should have quickly removed all of the dashes from your Excel data.
Before using this method, there are a few things to note:
- Because the Find and Replace feature replaces your original data, you will want to make sure you have a backup copy in case you need to restore the original data again.
- If any of your numbers start with zero, Excel will most likely remove those leading zeros. To fix this, you simply need to fix the format of the cell to show at least 9 digits.
Using a Formula to Remove Dashes in Excel
The second method that I like to use to remove dashes or hyphens in excel is by formula.
Here is the formula you would use:
=SUBSTITUTE(A2,”-“,””)
Let’s better understand what this formula is trying to do:
- A2 is for the cell in which you want to remove the dashes. This could also be A2 – A 6 if you are editing multiple cells at once.
- “-” is emphasizing the item to be removed, in this case the dash.
- “” is telling Excel that we want to replace the dash with nothing (null).
Let’s use the same example set of SSN data and see how this works in Excel:
As you can see, the formula successfully removed the dashes from the SSN listed in the data.
As mentioned before, this also works great with phone numbers or even addresses, other type of ID numbers, and more.
These two methods are quick and easy for removing dashes and hyphens from a large set of data.
If you know any other great methods, please share them in the comments below!
