How to Remove Dashes (-) in Excel?

by

Sometimes, you may get data (text or numbers) that have dashes (or hyphens) in it.

If you want to remove these dashes in Excel, you can use a couple of methods.

And no… doing it manually is not one of the options I cover here.

This can be particularly useful when you get social security numbers data (which is in AAA-GG-SSSS format), and you want to remove these dashes and just get the number.

Let me show you two really simple ways to do this.

Remove Dashes using Find and Replace

The easiest way to remove dashes in Excel is to use the Find and Replace feature.

As the name suggests, you can find all the cells that have a dash (or hyphen) in them and then replace them with a space character or a null string.

Let’s see how to do this.

Suppose you have SSN data, as shown below, and you want to remove the dash and only get the numbers.

SSN-Dataset-to-remove-dash-in-Excel

Below are the steps to use find and replace to remove dashes in Excel:

  1. Select the dataset from which you want to remove the dashes
  2. Hold the Control key and then press the H key. This will open the Find and Replace dialog box.
  3. In the ‘Find what’ field, type the dash symbol (-)Find-what-field-enter-the-dash
  4. Leave the ‘Replace with’ field emptyreplace-with-field-leave-blank
  5. Click on Replace All.

The above steps will remove all the dashes from the cells, and you will be left with the numbers only.

This is a really quick method to remove dashes in Excel, but there are a couple of things you need to know when using it:

  • It changes the original data. In case you may need the original data later, make sure you create a backup copy before doing this.
  • When your number starts with a 0 (for example, in 087-54-7465), this method will give you the result where the leading zero has been removed. This is something Excel does (try entering 001 in a cell, and you will see it is changed to 1 only). This is because earlier (when there were dashes in the cell), Excel considered those as text and didn’t change anything. But when dashes are removed, Excel starts considering it as a number, and the leading zeros are removed.
Also read: How to Remove Question Marks from Text in Excel?

Remove Dashes using Formula

Another simple method to get rid of dashes in Excel is to use formulas.

Suppose you have SSN data as shown below, and you want to remove the dash and only get the numbers.

Dataset-to-remove-dash-in-Excel

The below Excel formula will do this:

=SUBSTITUTE(A2,"-","")
Substitute-formula-to-remove-dashes-hyphens-in-Excel

The above SUBSTITUTE formula takes three arguments:

  • A2 – the cell from which you want to remove the text string (dashes in this example)
  • “-” – The string you want to remove (you need to place it in double quotes)
  • “” – What you want to replace it with. In this example, I want to substitute dashes with blank (null string)

The above formula looks for the dash (or hyphen or whatever you specify) in the cell and replaces it with a null string (which essentially means it removes all the dashes in the cell).

In this example, I have shown you how to remove dashes from a column using a formula. If you have your data in a row, you can use the same formula in the left-most cell and then copy the formula for the row (instead of the column as shown above)

Note: This formula will only work when the dashes in Excel are actually entered in the cell. If these dashes appear as part of the cell formatting, then this formula will not be able to work and you will have to change the cell formatting to get rid of the dashes.

Why Dashes Appear in Excel

When working with data in Excel, you may come across cells containing dashes (-) instead of numbers or text.

These dashes can appear for a variety of reasons, including:

  • Data Entry Errors: Dashes may appear in Excel when data is entered incorrectly. For example, if a user accidentally types a dash instead of a number or text, Excel will interpret it as a dash.
  • Importing Data: Dashes may also appear when data is imported into Excel from external sources, such as a CSV file or a database. In some cases, the software used to export the data may use dashes as a placeholder for missing or unknown values.
  • Formatting: Dashes may also be used in Excel to indicate negative numbers or as a separator in phone numbers or social security numbers.

Regardless of the reason why dashes appear in Excel, they can make it difficult to work with the data and perform calculations.

Fortunately, using the methods covered in this article, you can remove dashes from Excel cells.

Other Excel tutorials you may find useful:

Leave a Comment