How To Remove Dashes From Phone Numbers In Excel

When working with information in excel (numbers or text), there may be times where the information has dashes or hyphens that need to be removed.

For example, say yous take a big group of social security numbers in the following format: 123-456-789.

You need to remove all of the "-" symbols and just keep the numbers.

If you are thinking almost going into the spreadsheet and removing each nuance 1 by i, think again.

Removing dashes and hyphens one by ane is simply not an pick when you have large amounts of data.

It is a tedious and time consuming task.

Worry non, in that location are a couple of other methods that are simple and constructive in removing dashes and/or hyphens from your data so that you are merely left with numbers or text.

Permit'due south wait at ii simple ways to remove dashes or hyphens in Excel.

Removing Dashes or Hyphens in Excel using the Observe and Replace characteristic

Excel already has a groovy niggling feature called "Notice and Replace."

Simply as the name suggests, this characteristic allows you to observe all of the cells that have a nuance or hyphen in them, and then replace information technology with either a space grapheme or a null (blank).

Allow's await at an example using SNN numbers. This tin can besides be done with phone numbers or whatsoever other blazon of information:

Using the above SSN data fix, nosotros are going to utilize the Find and Supercede feature to remove all of the dashes in Cavalcade A:

Select the dataset where you want the dashes to be removed (in this example we are selecting A(i-6)
While holding downwardly the Control Key, press the H key. This should have opened the Observe and Replace feature dialog box.
In the field "Find What", yous want to type in the dash symbol (-).
The "Supplant with" field remains empty.
Next, select Replace All in the lesser left corner.

This method should have quickly removed all of the dashes from your Excel data.

Before using this method, there are a few things to note:

Considering the Notice and Replace feature replaces your original data, you volition want to make certain you have a backup re-create in example yous need to restore the original data once again.
If whatsoever of your numbers start with zero, Excel volition most probable remove those leading zeros. To fix this, you simply need to fix the format of the cell to show at least nine digits.

Using a Formula to Remove Dashes in Excel

The 2nd method that I similar to utilize to remove dashes or hyphens in excel is by formula.

Here is the formula yous would use:

=SUBSTITUTE(A2,"-","")

Allow's better understand what this formula is trying to exercise:

A2 is for the cell in which yous want to remove the dashes. This could also be A2 – A half-dozen if you are editing multiple cells at once.
"-" is emphasizing the particular to be removed, in this case the dash.
"" is telling Excel that we want to supplant the dash with nothing (zilch).

Let's use the same example gear up of SSN data and see how this works in Excel: