5 Ways to Easily Remove Dashes in Excel

If you frequently work with datasets in Excel, you might have come across the need to remove dashes from various types of data, like phone numbers, identification numbers, or codes. Excel offers several straightforward methods to help you clean and standardize your data. Let’s delve into five effective ways to easily remove dashes in Excel, ensuring your data looks consistent and professional.

1. Using the Find and Replace Feature

The Find and Replace tool in Excel is perhaps the most intuitive method for removing dashes:

• Select the range of cells where you want to remove dashes.
• Press Ctrl + H to open the Find and Replace dialog box.
• In the ‘Find what’ field, enter - (the dash character).
• Leave the ‘Replace with’ field blank.
• Click on Replace All to remove all dashes from your selected cells.

💡 Note: This method works great when you’re dealing with a specific character you need to remove throughout your data.

2. Utilizing Text to Columns

Excel’s Text to Columns feature can also serve to remove dashes, especially if your data has a consistent format:

• Select the column where dashes need to be removed.
• Go to Data > Text to Columns.
• Choose Delimited and click Next.
• Check the box for Other and type a dash into the box next to it.
• Click Next again, then select a destination for the results, typically overwriting the existing column.

By doing this, Excel splits your data at each dash point, and then you can concatenate the resulting columns without dashes.

3. Excel Formulas to Strip Dashes

Formulas can be particularly useful when you need to dynamically update your data:

• Using SUBSTITUTE: =SUBSTITUTE(A1,“-”,“”) removes all dashes from the text in A1.
• Using CHAR: To remove dashes, =SUBSTITUTE(A1,CHAR(45),“”), where CHAR(45) is the ASCII code for the dash.

You can apply these formulas to entire columns by dragging the corner of the formula cell down.

4. Power Query for Data Transformation

Power Query, an advanced tool in Excel, provides powerful data transformation capabilities:

• Select the range or table you wish to transform.
• Go to Data > From Table/Range.
• Once in Power Query Editor, select the column, then:
• Go to Transform > Replace Values.
• Enter a dash in the ‘Value to Find’ field and leave ‘Replace With’ blank.
• Apply the transformation.
• After performing your transformations, you can close and load the transformed data back into Excel.

💡 Note: Power Query is especially useful when dealing with large datasets or when you need to perform the same transformations repeatedly.

5. VBA Macro to Automate Removal

For users comfortable with coding, a VBA (Visual Basic for Applications) macro can automate the dash removal process:

Sub RemoveDashes()
    Dim rng As Range
    Dim cell As Range

'Set the range to work with
Set rng = Selection

'Loop through each cell in the selected range
For Each cell In rng
    'Replace dashes with nothing
    cell.Value = Replace(cell.Value, "-", "")
Next cell


End Sub

Run this macro after selecting the cells you want to modify. VBA macros can also be edited or expanded to include other transformations.

In summary, Excel provides multiple avenues for removing dashes from data, catering to different user preferences and the level of complexity in the task at hand. Whether you opt for simple find-and-replace, use formulas for dynamic solutions, or employ advanced features like Power Query or VBA, Excel empowers you to manage and clean your data efficiently. By mastering these techniques, you ensure your datasets are clean, consistent, and primed for further analysis or reporting.

Related Terms:

• Remove dashes in Excel formula
• Remove dashes online
• excel remove hyphens from numbers
• remove dashes from excel worksheet
• excel remove hyphen from string
• excel remove first character