How to Remove Dashes (-) in Excel?

Excel is a great tool when it comes to data manipulation. There are many functionalities and functions in Excel that allow you to modify your data to get the desired result quickly.

One common scenario is when you get your data with dashes/hyphens, and you want to remove them.

For example, below, I have the dummy data in the Social Security Numbers format where I want to remove the dashes in between the numbers.

In this tutorial, I will show you four easy methods to remove dashes in Excel.

Method 1 – Remove Dashes Using Flash Fill

If you have a consistent dataset, then the easiest way to remove dashes would be by using Flash Fill.

Flash Fill works by identifying patterns and giving you the result once you enter the expected result in one or two cells.

Let me show you how it works with a simple example.

Below I have a data set where I have the SSNs with dashes, and I want to remove these dashes and only get the numbers.

Here are the steps to do this:

1. Enter the expected result in Cell B2 (which is the first cell adjacent to our data set where we have the numbers from which we want to remove the dashes)

2. Hit the Enter key. This will bring the cursor to cell B3.
3. Hold the Control key and press the E key. This is the shortcut to use Flash Fill in Excel. Alternatively, you can also click the Home tab, click on the Fill icon in the Editing group, and then click on the Flash Fill icon.

The above steps would instantly fill the entire column with the expected results (where the dashes have been removed).

This is made possible as Flash Fill was able to identify the pattern when we entered the data in cell B2, and then replicate the same while giving us the result in all the cells in the column.

You can also use Flash Fill to remove some of the dashes. For example, if I only want to get rid of the first dash while keeping the second one, I can do that by entering 695-77990 in cell B2 and then using Flash Fill.

Note: While Flash Fill is quite good at identifying patterns, it may not always be correct. So make sure to check that you have got the right result. If it’s giving you the wrong result, try entering the expected result in the first two cells and then use Flash Fill.

Also read: Remove Parentheses in Excel

Method 2 – Remove Dashes Using Find and Replace

Another fast way to quickly remove dashes from a cell is by using the Find and Replace technique (where we find the dash and replace it with a null string)

Let me show you how it works by using a simple example.

Below I have a data set where I have numbers in column A that have dashes in between, and I want to get rid of these dashes:

Here are the steps to do this:

1. Select the cells that have the data from which you want to remove the dashes
2. Click the Home tab

3. In the Editing group, click on the Find and Select option, and then click on Replace. This will open the Find and Replace dialog box.

Tip: You can also use the keyboard shortcut Control + F to open the Find and Replace dialog box

4. In the Find what field, enter a dash character (this is what we want to find and then remove from the cells)

5. Leave the ‘Replace with’ field empty.

6. Click on Replace All button.

7. It will show a message box mentioning how many dashes have been removed. Click OK.

The above steps would remove all the dashes from all the selected cells, and you would be left with the numbers only.

Note: One limitation of this method is that you cannot use it to remove the dashes selectively. For example, you cannot choose to remove only the first or the last dash. When Find and Replace is used, it will remove all the dashes in the selected range.

Method 3 – Remove Dashes Using Formula

Another useful method to remove dashes/hyphens in Excel is by using the SUBSTITUTE function.

You should consider using it when you want more control over the result.

Let me show you how it works.

Below I have a data set where I have numbers with dashes in column A, and I want to use a formula to remove the dashes and get the result in column B.

Here is the formula that would remove the dashes from the cell:

=SUBSTITUTE(A2,"-","")

Enter this formula in cell B2, and copy it for all the other cells in the column.

In the above SUBSTITUTE formula:

• The first argument is the cell reference with the text from which I want to remove the dash.
• In the second argument, I have specified dash as the text string that I want to substitute (in double quotes)
• In the third argument, I have specified the text with which I want to replace the dash. Since all I want to do is remove the dashes, this would be a null string in double quotes.
• There is also a fourth optional argument (which I have not used here). In this argument, you can specify what instance of the dash you want to remove. So if you want to remove only the first dash, you can enter 1 as the fourth argument, and if you only want to remove the second one, you can use 2. When you don’t specify this argument, it will remove all the instances of the dash symbol.

Method 4 – Remove Dashes Using Power Query

Another method you can use to remove dashes and hyphens from your data set is by using Power Query.

This is not the most straightforward method and would only be useful if you’re already using Power Query as part of your work.

For example, if you’re already using Power Query to get the data from a database or from an Excel table and transform it, then you can use the steps I cover here to learn how to remove the dashes from that data as well.

Another scenario where this Power Query method could be useful is when you have to do this quite often. So you can create a flow using Power Query, and then whenever you get new data, you can change the source data and refresh the query.

Let me show you how to use Power Query to remove dashes in Excel.

Below I have a dataset where I have dashes in column A that I want to remove.

Here are the steps to do this using Power Query:

1. First, we will need to convert this into an Excel table (so that we can use this in Power Query). To do this, select the entire dataset.
2. Click the Insert tab in the ribbon
3. Click on the Table option

4. In the Create Table dialog box that opens, make sure that the range is correct, and then click OK.

5. Click the Data tab
6. Click on the From Table/Range option. This will open the Power Query editor.

7. Right-click on the column header
8. Click on Replace Values option.

9. Enter a dash character in the ‘Value to Find’ field

10. Leave the Replace with field empty

11. Click OK. You will notice that all the dashes have been removed from the data.

12. Click on the Close and Load option in the ribbon.

The above steps will insert a new worksheet in the workbook and give us the resulting table in this new worksheet.

While this method does have a lot of steps, it is useful in case you keep getting new datasets from which you want to remove dashes.

With Power Query, you can create this process once, and then whenever you get a new dataset, or there are changes in the original data set, you do not have to repeat the entire process.

Just go to the new table that has been inserted in the new sheet, right-click, and then click on refresh. It will repeat all the processes in the back end, go to the original data, remove the dashes and give you the new result.

In this tutorial, I showed you four different ways to quickly remove dashes from a data set in Excel.

If you do this once in a while and want to get this done quickly, you can use the Flash Fill (Method #1) or the Find & Replace (Method #2).

If you want a little more control over the result, you can consider using the SUBSTITUTE formula (Method #3). And if this is something you need to do quite often on a regular basis, you can also automate the process by using Power Query (Method #4).

Other Excel articles you may also like:

• How to Remove Comma in Excel (from Text and Numbers)
• How to Remove Line Breaks in Excel
• How To Remove Text Before Or After a Specific Character In Excel
• How to Remove the First Character from a String in Excel
• Separate First and Last Name in Excel (Split Names Using Formulas)