How To Remove Dashes in Excel? [Declutter Sheets]

When you import data from other sources into Excel, it may not support the formatting and add unnecessary characters like dashes (-). Besides, many people habitually put a hyphen in a cell with no value to denote it as blank.

Of course, removing them individually can take a lot of time, especially when you’re dealing with large data sets of SSNs, phone numbers, etc. Thankfully, Excel has recognized this problem and provided four different solutions to it.

Let me walk you through each, one by one.

Method 1: Using Flash Fill

If the values in your cells are in a particular manner and if it is consistent across the cells then you can remove the dashes with the Flash Fill option.

Flash Fill is built to identify a certain pattern once you manually enter the data to a corresponding (or adjoining) cell.

Given that the characters or numbers in the cells are separated evenly with a dash, here is how you can remove hyphens in Excel using the Flash Fill option:

1. Enter all the source data in one range of a column.
2. Now go to the adjacent cell to the first cell of the range.
3. Enter the value manually without the dashes.
4. Press the Enter key, which will take you to the cell below, which is also an adjacent cell to the second cell of the range.
5. Press Ctrl + E or go to Fill from the ribbon menu and select “Flash Fill”. This will remove the dashes in the adjacent cells.
   

Method 2: Using Find and Replace

Find and Replace, as the name suggests, is a very effective feature in Excel that finds the value you enter and replaces it with a value you put.

The key to replacing a dash with the help of Find and Replace is by replacing the dash with a null value. Here is how you can use Find and Replace to get rid of unwanted dashes:

1. Enter all the source data in one range of a column.
2. Select the range of cells where the data is entered.
3. Locate and click on “Find and Select” in the “Home” tab.
4. Alternatively, you can use the keyboard shortcut to find which is Ctrl + F.
5. Go to the “Replace” tab in the “Find” tab in the pop-up.
   
6. Enter dash “-” in the “Find what” data field.
7. Leave the “Replace with” data field empty.
8. Now click on “Replace All”.
   
9. This will trigger the sheet to find the dashes from the selected range and replace them with the entered value, which in this case, was empty.

Method 3: Using a Formula

If you don’t want to manually select the data range and use the Find and Replace option every time, then there are a couple of formulas to remove dashes. SUBSTITUTE essentially does the same thing but in a refined manner.

Let’s understand with steps:

1. Enter all the source data in one range of a column.
2. Now go to the adjacent cell to the first cell of the range.
3. Enter the syntax SUBSTITUTE(cell/range, “-”, “”) in the formula bar or directly in the cell.
   
4. Now drag the bottom right corner of that cell all the way to the range, which will implement the same formula for values in respective cells.

Method 4: Using a Plugin

There are some plugins designed to perform specific tasks, like replacing data values. Kutools (not compatible with Mac) is one such fine plugin that helps you get rid of unnecessary dashes and values from your Excel sheets.

Firstly you need to download the plugin from its official website and install it using basic installation setup.

1. Go to “Add-ins”.
2. Go to the more options.
   
3. Enter the plugin name in the search.
   
4. Select the range of cells where you want to remove dashes from.
5. Go to the plugin’s tab.
6. Select “Remove Characters” from the dropdown.
7. Select “Custom” from the pop-up.
   
8. Insert dash “-” in the field.
9. Click “Remove”. This will remove the entered value, which in your case is a dash.

If you want to further declutter your sheet, you can delete multiple cells, move rows, remove dotted lines, and try other stuff.

Wrap Up!

Taking dashes out makes your Excel sheets uncluttered and prevents any calculation or formatting errors that occur when the formulas or formatting styles conflict with dashes. I hope you found this article helpful. If you have any queries, feel free to comment below.