How to remove dashes in Excel

There can be some situations where you want to remove dashes from your data in Excel. For example, data that contains the SSN numbers. There are different ways to remove dashes from your data in Excel. This article shows you how to remove dashes in Excel.

How to remove dashes in Excel

Here, we will show you the following ways to remove dashes in Excel:

Using the Flash Fill method
Using the Find and Replace method
Using the SUBSTITUTE function

Let’s see all these fixes in detail.

1] Remove dashes in Excel by using the Flash Fill method

This is the easiest method to remove dashes from data in Excel. Flash Fill identifies the pattern applied to a particular cell and then it applies it to the remaining cells.

To use Flash Fill, first, enter the value in the targeted cell manually by removing the dashes (refer to the above screenshot). Now, we will use the Flash Fill method. The shortcut to use the Flash Fill is Ctrl + E. Select the cell in which you have entered the data manually without dashes and then press the Ctrl + E keys. Excel will automatically fill data without dashes in the remaining cells.

Alternatively, you can also select the Flash Fill under the Home tab. First, select the cell in which you have manually entered the data without dashes. Now, go to the Home tab and then select “Fill > Flash Fill.” You will find this option under the Editing group.

Flash Fill may fill incorrect data. When you notice such a thing, fill the first two cells manually without dashes, then select both of these cells and then use Flash Fill.

2] Remove dashes in Excel by using the Find and Replace method

This method does not work if a particular format is already applied to your cells, say SSN (Social Security Number) format. In such a case, you can remove dashes by using the other methods explained in this post or by removing the format. We will explain how to remove the format later. First, let’s see how to remove dashes in Excel by using the Find and Replace method.

Go to the Home tab. Under the Editing group, click Find & Select > Replace. A new Find and Replace window will open. Alternatively, you can also open the Find and Replace window by pressing the Ctrl + F keys.

In the Find and Replace window, select the Replace tab. Type dash (-) in the Find what field. Leave the Replace with field empty. Now, click Replace All. Do note that this action will remove the dashes in the entire Excel sheet. If you want to remove dashes from some particular cells, select those cells first and then use the Find and Replace feature.

Also, the Find and Replace will overwrite the data. This means that the old data will be replaced with the new data.

Above, we have mentioned that the Find and Replace feature does not work with the formatted cells. In this case, you have to remove or change the formatting. To explain to you, we have created sample data in which we have applied SSN format to the cells (refer to the above screenshot). If you see the Formula bar, you will notice that dashes do not appear there. This is because the SSN format is applied to the cells.

Now, select the cells from which you want to remove the dash (-). Right-click on the selected cells and select Format Cells. The Format Cells window will appear. Select General and click OK. This will remove the formatting style from the selected cells. You can also see the preview on the right side after selecting a particular format.

3] Replace dashes in Excel by using the SUBSTITUTE function

The SUBSTITUTE function in Excel is used to replace a particular text with another text. Here, we will show you how to use the SUBSTITUTE function to remove dashes in Excel. This method also works with SSNs.

Select the cell in which you want Excel to display the result. Now, type the following formula:

=SUBSTITUTE(cell reference,"-","")

Here, cell reference is the cell that contains the number with dashes. For example, in our case, it is the cell A1. Hence, the formula looks like this:

=SUBSTITUTE(A1,"-","")

Now, copy the formula to the remaining cells in Excel by using the Fill Handle. Fill Handle quickly copies the formula to other cells. Place your mouse cursor at the bottom right side until it changes to the black Plus icon. Now, press and hold the left mouse click, and drag the cursor to the bottom. Release the left mouse click to fill in the data.

Read: How to reduce the size of an Excel file.

How do I remove SSN dashes in Excel?

You can remove SSN dashes in Excel by removing the formatting style applied to the cells or by using the SUBSTITUTE function in Excel. The Find and Replace method will not work here.

How do you change a dash to 0 in Excel?

You can replace a dash with 0 in Excel by using the Find and Replace feature. First, copy the data to another sheet or cells. Now, select the cells in which you want to replace the dash with 0. Now, press the Ctrl + F keys to open the Find and Replace window. Under the Replace tab, enter – in the “Find what” field and enter 0 in the “Replace with” field. Now. click Replace All.