How to Remove Dashes in Excel?

Excel is a reliable tool for data manipulation and analysis which is applicable across industries and professions.

However, dealing with data inconsistencies, such as dashes (“-“) in your worksheets, can be challenging. These dashes often occur when importing data or due to certain formatting issues. 

This comprehensive guide will explore different methods to remove dashes in Excel and ensure clean, consistent data for your analysis.

Before we proceed with removing dashes, it’s crucial to identify their presence in your Excel worksheet. You can manually scan the data or use the Excel Find Feature (Shortcut key: Ctrl+F) to search for dashes (-)

For e.g., if we have a data set which Social Security Numbers (SSN) in column A, using the Find feature (Ctrl+F) and entering (-) in the Find bar and clicking the Find all tab will indicate the cells in column A.

Dataset for removing dashes in excel
Find function to identify dashes

Now that we have identified the dashes, we will look into various methods that can be used to remove dashes in your worksheets.

Method 1: Find and Replace to Remove Dahshes

Find and Replace is a versatile feature in Excel that enables you to search for specific characters or text and replace them with alternate content.

When it comes to removing dashes, Find and Replace stands as one of the most straightforward and effective techniques.

Here’s a stepwise guide to using Find and Replace to remove dashes in Excel:

Step 1: We start by creating another column in which we copy the SSN data from Column A to Column B. Now Select the cells containing the data with dashes in Column B.

Things to note: By selecting the column, we will ensure that the Find and Replace option only replaces the dash in the Result column. Else, all the dashes in the sheet will get replaced.

Dataset with new column for removing dashes

Step 2: Press Ctrl + H to open the Find and Replace dialog box. You need to hold the Control key and then press the H key

Find and replace function to remove dashes

Step 3: In the “Find what” field, enter the dash character (-).

Replace function to remove dashes

Leave the “Replace with” field empty or enter the desired replacement text. Since we are removing the dashes here, we will leave the field empty.

Replace dashes with blank

You can also click on the “Options” tab to tweak the search settings. This lets you choose whether you want to replace dashes only in a specific area or in the whole workbook, depending on what you need. It’s pretty handy for customizing the way you remove those dashes!

Options in the Replace option

Step 3: Now click on “Replace All” to remove all the dashes.

Using Replace All to remove dashes

Step 4: This will display the set of values without the dashes. We are using a different column called “Result” for representation.

Result column without dashes

It’s important to be cautious when using the Find and Replace method to remove dashes in Excel. While this method is simple and effective, it can lead to unintended replacements if dashes exist within other words or phrases. For example, if your data contains phrases like “data-entry,” removing all dashes would result in the phrase becoming “dataentry.” The best approach for using Find and Replace is to select the cells where you want to remove dashes and limit the Find and Replace search parameters to “Within the sheet”

One drawback of this method when using it to remove dashes with numbers that have leading zeros is that the leading zeros would be removed. For example, if you have the text 001-222-342 and you remove dashes from this, the result would be 122342 (where the leading zeros have been removed.

Method 2: SUBSTITUTE Function to Remove Dashes 

The SUBSTITUTE function allows to replace specific text within a string. It can be utilized to remove dashes from data. 

Let’s again start with the same dataset, which has SSN with dashes in Column A. 

Dataset for removing dashes in excel

Here’s how to use the SUBSTITUTE function:

Step 1: In a new column named Result, enter the formula 

=SUBSTITUTE(A2, "-", "")
Substitute functions to remove dashes

Step 2: Drag the formula down to apply it to the remaining cells. Select the formula cell, click and hold the fill handle, then drag to apply the formula to desired cells and release the mouse button to finish.

The SUBSTITUTE function will replace all the dashes with an empty string, effectively removing them.

Output after removing dashes

Advantages of using the SUBSTITUTE formula in Excel:

  • Flexible Text Replacement: The SUBSTITUTE formula allows you to replace specific text within a cell or a range of cells. It provides flexibility in replacing occurrences of a particular substring, making it suitable for various data cleaning and manipulation tasks.
  • No Impact on Original Data: Unlike other methods, the SUBSTITUTE formula does not modify the original data. It only returns a modified version of the text while preserving the original content, ensuring data integrity.

Considerations when using the SUBSTITUTE formula:

  • No Global Replacement: Unlike some other methods like Find and Replace, the SUBSTITUTE formula does not have a global replace option. You need to apply the formula cell by cell or use additional functions to achieve a global replacement across a range.
  • String Length Limitations: The SUBSTITUTE formula has limitations on the length of the text it can handle. Extremely long strings may cause errors or not be processed correctly.

In summary, the SUBSTITUTE formula is a powerful tool for basic text replacements in Excel, offering flexibility and ease of use.

However, for more complex data manipulation tasks, you may need to explore additional Excel functions or methods.

Also read: Show Negative Numbers in Parenthesis in Excel

Method 3: Flash Fill to Remove Dashes in Excel

Flash Fill is a fantastic feature that automatically fills in values based on a pattern. It can be incredibly useful when dealing with dashes or other unwanted characters. 

How to use Flash Fill with Keyboard Shortcut

Step 1: Enter a new column next to the column containing the data with dashes. Here we are using the name “Result”.

Step 2: In the new column “Result”, start typing the corrected values to establish the desired pattern. You can then press the keyboard shortcut “Ctrl+E”, to implement Flash Fill. 

Step 3: Excel will automatically detect the pattern and fill in the remaining values in the column.

Note: Remember that you have to manually update the first cell without the dashes and then use this option.

Output after using Flash fill to remove dashes

Alternative Ways of Using Flash Fill

If you don’t want to memorize another keyboard shortcut, you can use these alternate methods to use Flash Fill

Alternate 1: Click on “Home” and select “Fill”. Under the “Fill” section, you will find a drop-down that displays “Flash Fill”. Now click on Flash Fill.

Using Flash Fill through Home tab

After clicking on “Flash Fill”, you will find all the remaining cells filled with data without the dashes. 

Output after using flash fill to remove dashes

Alternate 2: Select “Data” and under the tab and click on “Flash Fill”. 

Using Flash Fill in Data tab

Excel will fill in the remaining cells with the relevant result without the dashes, as shown below. 

Output after flash fill
Also read: How to Create a QR Code in Excel

Method 4: Remove Dashes with Excel Power Query

Excel Power Query is a powerful data transformation and cleansing tool. It allows us to connect to various data sources, apply transformations, and load the cleaned data back into Excel.   

Here’s how to use Excel Power Query to remove dashes:

Step 1: We start by creating another column in which we copy the SSN data from Column A to Column B. Now Select the cells containing the data with dashes in Column B.

Helper column with copy of the data

Step 2:  Now select the data in columns A and B, and insert a table.

Create Table for the dataset

Step 3: Go to the “Data” tab and click on the “From Table/Range” button. 

Step 4: In the Power Query Editor, select the column with the data containing dashes. Go to the “Transform” tab and click the “Replace Values” button.

Using Transform option in Power query to remove dashes

Step 5: Enter the dash character in the “Value to Find” field and leave the “Replace with” field empty. Click “OK” to remove all the dashes from the selected column.

Replace values within Power query
Output using replace function in power query

Step 5: Close the Power Query Editor and Click on “Close and Load” to return to the Excel. The output will come in a new Excel sheet. 

Output after using Power query to remove dashes

Using Power Query to remove dashes in Excel offers several advantages:

  • Data Refresh and Automation: Power Query allows you to refresh the data with a single click, ensuring that the results remain up-to-date whenever the source data changes. Additionally, you can automate data transformation processes, saving time and effort.
  • Wide Range of Data Sources: Power Query supports connecting to various data sources, such as Excel workbooks, CSV files, databases, websites, and more. This versatility enables you to clean and transform data from different sources, including data with dashes.
  • Advanced Data Transformation: Power Query provides a vast array of data transformation options, including custom formulas, conditional columns, merging queries, and more. This allows you to perform complex data manipulations beyond simple dash removal.
  • Data Preview and Validation: Power Query offers data preview features, allowing you to see the changes before applying them. This helps validate the transformation steps and ensures you get the desired results.
  • Reusable Queries: Once you create a Power Query to remove dashes, you can save it as a reusable query. This way, you can apply the same transformation to other datasets without recreating the process, promoting efficiency and consistency.
  • Editable and Adjustable: Power Query queries are easily editable and adjustable. If you need to modify the transformation logic, you can do so effortlessly within the Power Query Editor.
  • Recordable and Shareable: Power Query transformations can be recorded and saved as part of the Excel workbook. This means you can share the workbook with others, and they can replicate the data cleaning process using the same Power Query steps.

Method 5: Remove Dashes with VBA

To remove dashes in Excel using VBA, you can create a simple code that loops through the selected range or a specific column and replaces dashes with empty strings. Here’s a step-by-step guide:

  1. Open your Excel workbook and press “Alt + F11” to open the Visual Basic for Applications (VBA) editor.
  2. In the VBA editor, click “Insert” from the menu and select “Module” to insert a new module.
  3. In the module window, paste the following VBA macro code:
Sub RemoveDashes()
    Dim rng As Range
    Dim cell As Range
        'Change B2:B10 to the range or column where you want to remove dashes
    Set rng = Range("B2:B10")
        'Loop through each cell in the range
    For Each cell In rng
        'Check if the cell contains a dash ("-")
        If InStr(1, cell.Value, "-") > 0 Then
            'Replace the dash with an empty string
            cell.Value = Replace(cell.Value, "-", "")
        End If
    Next cell
End Sub
  1. Modify the line Set rng = Range(“B2:B10”) to specify the range or column where you want to remove dashes. For example, you can change it to Set rng = Range(“B:B”) to apply the macro to the entire column B.
  2. Close the VBA editor by clicking the “X” in the top-right corner or by pressing “Alt + Q.”
  3. To run the macro, press “Alt + F8” to open the “Macro” dialog. Select the “RemoveDashes” macro from the list and click “Run.”
Output using VBA to remove dashes

The macro will now loop through the specified range or column, check for dashes in each cell, and replace them with empty strings, effectively removing the dashes.

Remember to save your workbook with macros enabled (.xlsm format) to retain the VBA code and run the macro whenever needed.

In conclusion, removing dashes in Excel is essential to maintain clean data and ensure precise analysis.

Throughout this article, we discussed five different methods to accomplish this task.

Depending on your needs and preferences, select the method that best suits you. By applying these techniques, you can effortlessly eliminate dashes from your text, effectively clean your data, and enhance your productivity while working with Excel.

Some more Excel articles that might interest you:

Leave a Comment