If you work with an extensive data set containing redundant dashes that prevent Excel from treating those items as numeric values, there’s a quick way to fix the issue.
There are two quick-and-dirty ways to remove dashes in Excel. So without further ado, let’s dive right in.
The Find and Replace Tool Method
The easiest way to get rid of dashes in Excel is to use the Find and Replace tool.
1. Select the entire data table you want to rid of dashes – in our case, that’s A1:A7.
2. Go to the Insert tab.
3. In the Editing group, hit the “Find & Select” button.
4. Click “Replace” from the drop-down menu that appears.
5. Type “–” into the “Find what” field.
6. Leave the “Replace with” field empty.
7. Hit the “Replace All” button.
Note: If your number starts with a zero – for example, if the modified value equals “087-54-7465” – this method will remove the leading zero (type “001” into any empty cell to see the two leading zeros disappear once you go through the steps mapped out above).
The Custom Formula Method
Another simple method to remove dashes in Excel is to apply a simple custom formula, allowing you to extract numbers from any data set riddled with dashes.
1. In the adjacent cell to your data table, type the following formula: =SUBSTITUTE(A1,”-“,””)
Drag – or double-click – the fill handle to copy the formula into the remaining cells to see all the dashes between the numbers being removed.
How to Remove Dashes in Excel on Mac
For Mac users, the steps might differ a bit, but the process is still pretty straightforward. To pull off the task, we will use the Find and Replace tool againю
1. Highlight the cell that you want to format (A1:A6).
2. Open the “Find and Replace” dialog box by using the Control + H shortcut.
3. In the “Find what” field, type the dash symbol (-).
4. Leave the “Replace with” field empty.
5. Сlick on “Replace All.”
These steps will remove all the dashes from the highlighted cells, allowing you to manipulate with data however you see fit.
Congratulations! Now you know how to remove dashes in Excel.