How to quickly remove dashes from cells in Excel?

When working with Excel spreadsheets that contain lists of numbers such as zip codes, phone numbers, ISBNs, or product IDs, you may often encounter values with embedded dashes (hyphens) for formatting or readability. However, for certain tasks—such as importing to databases, running analysis, or preparing for data validation—you might need to remove all dashes from these strings. Doing this efficiently is important, especially when handling long lists or large datasets. Fortunately, Excel provides several practical ways to eliminate dashes from your cells. Below, you’ll find detailed methods and suitable scenarios for each solution, including both built-in approaches and specialized tools.

Remove dashes from cells with formula
Easily remove dashes from cells with Kutools for Excel
Remove dashes from cells with VBA code
Remove dashes using Excel’s Find and Replace feature

Remove dashes from cells with formula

Using the SUBSTITUTE function in Excel is a straightforward way to remove all dashes from a cell. This method is especially useful when you want to process a large number of cells quickly, and you prefer not to alter the original data. For example, if your column contains numbers like “123-45-6789”, applying SUBSTITUTE can output a text value with all dashes removed. One important benefit is that this approach preserves leading zeros and avoids converting long numbers into scientific notation, which can frequently occur with default Excel behavior.

1. In a blank cell adjacent to your data (for example, C1 if your data starts in A1), enter the following formula:

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

2. After entering the formula, press Enter. The formula will remove all dashes from the text in cell A1. To apply the same logic to the rest of your data, select cell C1, then move your cursor to the fill handle in the bottom right of the cell, and drag it down as far as needed to fill the rest of the column. This automatically removes dashes from each corresponding cell in the A column. See screenshot:

For a large dataset, you can double-click the fill handle to auto-fill the range down to the last adjacent value. If you need to convert the result cells back to regular values (instead of formulas), simply copy the output cells and use “Paste Special > Values” to overwrite the formulas with their results. This formula will treat everything as text, so numbers with leading zeros or large digit counts won’t lose their formatting.

Notes & troubleshooting: The SUBSTITUTE formula is non-destructive and leaves the original data unchanged, which is ideal when you wish to retain both the formatted and unformatted versions. Make sure to reference the correct column and adjust the cell references if your source data is not in column A. If your data includes more than just dashes—for example, spaces or other symbols—you can nest multiple SUBSTITUTE functions to remove additional unwanted characters.

Remove dashes from cells with Kutools for Excel

If you find formulas or VBA code complex or time-consuming, Kutools for Excel provides a direct and user-friendly solution. The Remove Characters feature is designed specifically for quickly deleting specific characters (such as dashes) from selected cells with just a few clicks. This is especially helpful for Excel beginners, occasional users, or anyone who values efficiency and simplicity in their workflow. Another significant advantage is that there’s no need to write formulas or remember syntax.

1. Start by selecting the range of cells from which you want to remove dashes. Then go to Kutools > Text > Remove Characters. This opens the tool’s dialog window. See screenshot:

2. In the Remove Characters dialog box, select the Custom option and type the dash character (“-”) into the text box. The Preview Pane will instantly show you what the results will look like after removing the dashes. Once you’re satisfied, click OK to instantly clean all dashes from the selected cells. See screenshot:

Note: If your cells contain leading zeros or lengthy digit strings (such as ISBN codes or zip codes), and you want to avoid converting these values into scientific notation or losing leading zeros, you should format the destination cells as Text before using the Remove Characters feature. Otherwise, Excel may auto-convert the result into scientific format or strip leading zeros. To do this, right-click on the cells, choose “Format Cells,” and select “Text” as shown in the screenshot below.

This Kutools feature efficiently handles large datasets and minimizes manual work, reducing the risk of errors. After processing, you can review results in the preview pane before applying changes, adding another layer of confidence and control to your workflow.

Remove dashes from cells with VBA code

If you’re comfortable with macros, using VBA code offers a highly flexible way to remove dashes from any selected range. This method is efficient for batch processing and automates repetitive tasks, making it suitable for advanced users or those handling regularly updated datasets. As with formulas, VBA will convert the cleaned values into text format by default, so leading zeros and long numbers are preserved.

1. Select the cells from which you want to remove dashes.

2. Press ALT + F11 to open the Microsoft Visual Basic for Applications editor window.

3. Click Insert > Module to create a new code module, and paste the following code into the module window:

VBA: remove dashes between numbers

Sub DeleteDashes()
'Update 20130907
Dim rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For Each rng In WorkRng
    rng.NumberFormat = "@"
    rng.Value = VBA.Replace(rng.Value, "-", "")
Next
Application.ScreenUpdating = True
End Sub

4. Press F5 to run the code. A dialog will prompt you to select the range you want to process. See screenshot:

5. Click OK, and all dashes will be removed from the chosen cells. The output values are text, ensuring that format-sensitive numbers like zip codes or IDs maintain their appearance. See screenshots:

When using this method, keep in mind that the macro makes direct changes to your worksheet. Consider saving your file beforehand or working on a backup if you need to keep the original data. If you receive a macro security warning when executing the code, adjust your macro settings in Excel to allow the script to run. This approach is especially practical for recurring tasks or cleaning up inconsistently formatted data in bulk.

Remove dashes using Excel’s Find and Replace feature

For a simple and quick solution, you can use Excel’s built-in Find and Replace function. This approach is suitable for occasional tasks or when you want to directly modify the content in place without formulas or add-ins. However, it should be used with care—especially if your selected range contains data with dashes that you do not want to remove. Find and Replace will process all dashes within the selected cells, so double-check your selection before applying.

1. Select the range of cells that contains the dashes you want to remove. If you want to process the whole worksheet, click on any single cell before beginning.

2. Press Ctrl + H to open the Find and Replace dialog box.

3. In the “Find what” field, type a dash character (“-”). Leave the “Replace with” field empty.

4. Click Replace All to remove all dashes from the selected range. A dialog box will pop up indicating how many replacements were made. Click OK to confirm and then close the dialog window. Your cells will now appear without any dashes.

This method is fast and suitable for most simple scenarios. However, if your data includes formulas or you need a non-destructive solution, consider using the formula or Kutools options instead. Also, remember to double-check results, as Find and Replace impacts all instances of the chosen character within your range.

In summary, choosing the best method depends on your specific needs:

• Formula (SUBSTITUTE): Best for creating a separate, dynamic column with dash-free data. Useful if you want to keep the original information unchanged.
• Kutools for Excel: Excellent for users who want an efficient, GUI-based solution suitable for large datasets, without writing formulas or code.
• VBA Macro: Ideal for automating dash removal over large or frequently updated ranges, suitable for advanced users or those comfortable with coding.
• Find and Replace: The quickest option for occasional manual edits but lacks preview and affects all matching characters indiscriminately within the selected cells.

When removing dashes, always consider formatting and leading zeros to avoid unintentional data changes. If unexpected results occur, check for hidden characters or formatting issues, and confirm your selections before removing or replacing data. Saving your work before making batch changes is recommended.

Related articles:

• Remove alpha characters from cells
• Remove non numeric characters from cells
• Remove numeric characters from cells
• Remove non-printable characters from cells
• Remove non-alpha characters from cells
• Quickly remove line breaks
• Remove non-alphanumeric characters