How to Remove the Dash in Excel: A Comprehensive Guide

Introduction

Dashes can be useful in Excel to separate text or numbers, but there may come a time when you need to remove them. Whether you’re working with data from a different source or simply want to clean up your spreadsheet, removing dashes can help make your data more readable and consistent.

In this comprehensive guide, we’ll explore various methods to remove dashes in Excel, including using the SUBSTITUTE function, Find and Replace, and Text to Columns. We’ll also cover how to remove leading or trailing dashes and how to handle dashes within text or numbers.

Using the SUBSTITUTE Function

The SUBSTITUTE function is a versatile tool for replacing characters or text strings in a cell. To remove dashes using SUBSTITUTE, follow these steps:

1. Select the cells containing the dashes you want to remove.
2. Go to the Formulas tab and click on the Function Library icon.
3. Search for and select the SUBSTITUTE function.
4. In the SUBSTITUTE function dialog box, enter the following arguments:
   • Text: Enter the cell reference or range of cells containing the dashes.
   • Old_text: Enter “-” (the dash character).
   • New_text: Enter “” (an empty string). This will replace the dashes with nothing.
5. Click OK to apply the function.

Using Find and Replace

Find and Replace is a simple and efficient method for removing dashes. Here’s how:

1. Highlight the cells with dashes.
2. Go to the Home tab and click on Find & Replace.
3. In the Find what field, enter “-“.
4. Leave the Replace with field empty.
5. Click Replace All.

Using Text to Columns

Text to Columns can be used to split text or numbers based on specific delimiters, including dashes. To remove dashes using Text to Columns:

1. Select the cells containing the dashes.
2. Go to the Data tab and click on Text to Columns.
3. In the Convert Text to Columns Wizard, select Delimited.
4. In the Delimiters section, check the box for Dash.
5. Click Next and then Finish.

Removing Leading or Trailing Dashes

To remove leading or trailing dashes, you can use a combination of the LEFT, RIGHT, and TRIM functions. Here’s how:

1. To remove leading dashes, use the formula: =TRIM(LEFT(cell_reference, LEN(cell_reference) – 1))
2. To remove trailing dashes, use the formula: =TRIM(RIGHT(cell_reference, LEN(cell_reference) – 1))

Handling Dashes within Text or Numbers

If the dashes are part of text or numbers, you may need to use a combination of the SUBSTITUTE and IF functions to remove them selectively. For example, to remove dashes from numbers but not text, you can use the following formula:

=IF(ISNUMBER(cell_reference), SUBSTITUTE(cell_reference, “-“, “”), cell_reference)

FAQ

How do I remove dashes from a specific column in Excel?

Select the column, go to the Home tab, click Find & Replace, enter “-” in Find what, leave Replace with empty, and click Replace All.

How do I remove dashes from all cells in a worksheet?

Click the Select All button on the Home tab, go to the Home tab, click Find & Replace, enter “-” in Find what, leave Replace with empty, and click Replace All.

How do I remove dashes from the beginning or end of a cell value?

Use the LEFT and RIGHT functions to remove leading and trailing dashes, respectively, and then use TRIM to remove any remaining spaces.

How do I remove dashes from numbers but not text in Excel?

Use the IF and ISNUMBER functions to check if a cell contains a number and apply the SUBSTITUTE function to remove dashes only from numbers.

How do I remove dashes from a cell that contains both text and numbers?

Use a combination of the SUBSTITUTE, IF, and ISNUMBER functions to selectively remove dashes from text or numbers within a cell.