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.

Another use case could be when you have phone numbers, and you want to remove dashes from the phone numbers.

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

Remove Dashes from SSN 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.

While this is a really quick method to remove dashes in Excel, remember that this method changes the original data.

If you may need the original data later, make sure you create a backup copy before doing this.

Also read: How to Replace Zero with Dash in Excel?

Limitation of Find and Replace Method – Leading zeros are removed

When your number starts with a 0 (for example, in 087-54-7465), the above method will give you the result where the leading zero has been removed.

So instead of 087547465, you will get 87547465, 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.

So how do you remove the dashes before removing the leading zero(s)?

You can’t use the Find and Replace technique for this, but the formula technique covered next works well with leading zeros.

Also read: How to Remove Question Marks from Text in Excel?

Remove Dashes from SSN 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. 

Also read: How to Remove Leading Spaces in Excel?

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment