Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

How to Remove Dashes (-) in Excel?

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 it and then replace it 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 would 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 would 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). If you need to change the format of the cell to show at least 9 digits.

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 is that it removes all the dashes in the cell)

You may also like the following Excel tutorials:

  • How to Remove Formulas in Excel (and keep the data)
  • How to Compare Two Columns in Excel (using VLOOKUP & IF)
  • How to subtract multiple cells from one cell in Excel?
  • How To Set Column Width in Inches in Excel?

The post How to Remove Dashes (-) in Excel? appeared first on Excel Tips & Tutorials.



This post first appeared on Excel If Statement Multiple Conditions Range, please read the originial post: here

Share the post

How to Remove Dashes (-) in Excel?

×

Subscribe to Excel If Statement Multiple Conditions Range

Get updates delivered right to your inbox!

Thank you for your subscription

×