How to Remove Dashes in Excel (8 Different Examples)

When you are working with data in Excel, dashes are often used to separate different parts of text or numbers, like a phone number, product code, or employee ID. Sometimes these dashes can complicate data analysis, especially when you need to combine the parts into a single string. Fortunately, Excel offers a variety of tools, from quick fill features and simple functions to advanced tools like Power Query, which can help remove dashes. In this article, we will guide you through several methods to remove dashes in Excel.

Key Takeaways

To remove dashes in Excel, here is one simple solution by using the Flash Fill feature.
➤ In the adjacent cell, write next to the data with dashes, and manually type the desired result for the first entry.
➤ Select the next cell below and press Ctrl + E to apply Flash Fill.
➤ Excel will automatically detect the pattern and fill the rest of the column, removing dashes.

If all the dashes appear in a fixed location in the dataset, you can use the Flash Fill feature to remove dashes easily. Furthermore, you can use the REPLACE function to clean dashes in Excel.

Imagine you have a dataset with employee information, including an Employee ID in column C, where the dash is consistently the third character. Now, we will use Flash Fill and the REPLACE function to remove dashes.

For a non-formula solution, you can use Excel's Flash Fill feature. This works by recognizing a pattern in the data you manually enter.

Excel will automatically detect the pattern (removing the dash) and fill the rest of the column, and provide a dash-free list of Employee IDs.

The REPLACE function specifies a starting position and the number of characters to replace. By replacing the dash with an empty string, we will clear dashes from the Employee IDs.

This formula targets cell C2, starts at the third character, replaces one character (the dash), and replaces it with nothing ("").

The entire column of Employee IDs is now clean and dash-free.

Sometimes you will get multiple dashes within a single cell, especially for phone numbers. To remove those, you can use the Find and Replace tool or apply the SUBSTITUTE function.

Imagine a dataset of some employees and their phone numbers in column C, where dashes appear multiple times within each entry.

The Find and Replace feature is the fastest way to remove all dashes from a selected range.

Thus, all dashes are removed, leaving only the digits in a clean format.

If you need to keep your original data intact and place the dash-free result in a new column, the SUBSTITUTE function is the formula-based solution.

This formula tells Excel to look at cell C2, find every instance of a dash ("-"), and replace it with nothing ("").

As a result, you will get the numbers only by cleaning all the dashes.

Sometimes, data entry errors happen due to double dashes (--) within a cell, and these need to be removed as well. The SUBSTITUTE function can be nested to handle both single and double dashes effectively.

Imagine an Employee Code dataset in column D, which contains both single dashes and double dashes (--).

The inner SUBSTITUTE first removes all double dashes (--) by replacing them with a blank, and the outer SUBSTITUTE then removes all remaining single dashes (-).

Finally, you will get clean data, removing all single and double dashes.

Data imported from external sources often contains extra spaces alongside dashes. You can combine the TRIM and SUBSTITUTE functions to clean both spaces and dashes.

Consider a phone number dataset in column C where dashes and extra spaces are present.

Here, the TRIM function first removes any leading or trailing spaces. The inner SUBSTITUTE then removes all spaces (" ") within the cleaned string. Finally, the outer SUBSTITUTE removes all remaining dashes ("-").

Thus, you will get the phone numbers, with all dashes and spaces removed.

Applying formulas again and again can be hectic. For cleaning a large amount of data, you can utilize the Find and Replace tool, the Power Query feature, and embed VBA code.

Start with a large table containing multiple columns (e.g., Invoice No, Customer ID, and Product Code) where dashes are present in many cells.

This method is similar to the single-column approach. But here we will apply across the entire sheet for cleaning bulk data.

The dashes are immediately removed from all selected cells in the Invoice No, Customer ID, and Product Code columns.

Power Query is an extremely powerful tool for importing, transforming, and cleaning large datasets without altering the source data. Here, we will use the tool to clear dashes and put the clean data in another sheet.

This opens the Power Query Editor.

The data grid instantly updates, showing the values without any dashes.

Finally, you will get the clean data in a new sheet, removing dashes.

If you need to remove dashes from various sheets, a simple VBA macro can provide a one-click solution.

Sub RemoveDashes()
Cells.Replace What:="-", Replacement:="", LookAt:=xlPart
End Sub

This code tells Excel to search the entire active sheet's cells for a dash ("-") and replace it with nothing ("").

The dashes are instantly removed from all cells on the active sheet, providing a clean dataset.

Downloadable Resources

Get our practice workbook or necessary files that we have worked with to prepare this article.

Concluding Words

Above, we have explored several methods to remove dashes in Excel. Excel’s Flash Fill tool can instantly remove dashes if the dashes are in a fixed position. The Find and Replace tool is mostly used for bulk data. Using the SUBSTITUTE function, you can remove dashes from any place in a cell. Cleaning dashes with extra spaces is easy with the combination of TRIM and SUBSTITUTE functions. On the other hand, the Power Query tool and VBA provide a quick solution for bulk data. If you have any questions, feel free to share them in the comments section below.