How to Remove Dashes in Excel (3 Easy Ways)

Stuck with uncalled-for hyphens? Dashes lining in between phone numbers, separating book name and author, punctuating product codes, forming unnecessary categories. Well, not unnecessary as it is, but you may require data in a rawer form. That calls for removing hyphens and we don't want to be selecting each value and hitting backspace. When will Excel come in handy? ALWAYS!

This tutorial is about removing one or all dashes from a single value or multiple values in Excel. Here you will find 3 quick and simple ways of ridding hyphened data. No reason we can't start.

We'll start with the problem. Have a look at the dataset below:

It looks relatively problem-free but if you read the title of this tutorial, it all snaps into place. We don't have the contact numbers in number form; the hyphens have rendered them in text form. The communication systems won't process the numbers including the hyphens. They need to go.

Note: This case example shows dealing with numbers containing hyphens. You may use the same methods for any value; numeric, alphabetic, or alpha-numeric text.

Remover pens at the ready!

Method #1 – Using Find and Replace Feature

Use the Find and Replace feature to remove dashes from values in Excel. The Find and Replace feature is used to find text and replace it with other text. We will find the dashes in our data with Find and Replace and switch it with blank text to eliminate the dashes. To remove dashes using Find and Replace, follow the steps given below:

• Select the relevant cells from where you want the dashes removed.
• If you do not make a selection at this point, Find and Replace will make replacements in the complete sheet.
• Click on the Find & Select button in the Editing group of the Home tab and select the Replace option in the menu. As a shortcut, you can press the Ctrl + H keys instead to open the Find and Replace dialog box.
• 
• In the dialog box, enter a hyphen in the Find what
• Then, select the Replace All
• The Replace with field needs to be empty so that the dashes can be swapped for blank text.
• 
• A notification will show the number of replacements made.
• Select the OK command and close the Find and Replace dialog box.

All the dashes from the cell selection will be removed.

Now do you notice something amiss? The last number is missing a digit compared to the rest. That is because originally the number was 078-912-345 but due to the inclusion of the hyphens, the numbers were text (also discernable from their alignment to the left).

When the dashes were removed, Excel changed the text to numbers (as noted from the right-side alignment) and numbers in Excel automatically do not carry leading zeroes. Therefore, the number 078912345 defaulted to 78912345.

However, there are other ways to keep the numbers as they are, and they are given in the rest of this tutorial. Also, note that Find and Replace removes all the dashes in a cell and you can't hand-pick, let's say, the removal of just the first dash in a value. The next solution should deal with both these pointers.

Note: For alphabetic text, the replacement mechanism may require a bit of judgment. E.g. if the hyphen from "Holly-Garfield" needs to be removed, you can't replace the hyphen with blank text. That would end up in "HollyGarfield". Instead of blank text, you'll need to enter a single space character to arrive at "Holly Garfield".

Note that the Replace with section has a space character added.

Method #2 – Using SUBSTITUTE Function

Remove dashes in Excel using the SUBSTITUTE function. The SUBSTITUTE function replaces part of a text string with the provided text. This will help us replace the dashes in the phone numbers with an empty text string.

Additionally, the output of the SUBSTITUTE function will be text, unlike the previous method which converted the dash-less strings into numbers. That way, if any number has leading zeros, they will be retained.

Here is the formula we are using with the SUBSTITUTE function to erase the dashes from a set of numbers:

In the formula, the SUBSTITUTE function takes the value in C4 and replaces all the hyphens (given in the second argument, enclosed in double-quotes "-") with an empty string (the third argument i.e. "").

After removing the hyphens by replacement, the SUBSTITUTE function returns the value as text:

This time, the leading zero in the last contact has stayed. However, in case you require the values as numbers, you can add the VALUE function in the formula to convert the number text strings to numbers. Wrap SUBSTITUTE in VALUE and our formula becomes:

Now aligned to the right, we have the contacts in number form:

We previously hinted at being able to delete a particular dash in a value instead of all of them. In our case example, suppose we want to keep the first dash that separates the area code and remove the second dash. The SUBSTITUTE function will be used to replace only the second occurrence of a hyphen with empty text, using this formula:

True to form, all the contact numbers have only retained the first dash separating the area code:

Method #3 – Using VBA

With VBA in Excel, you can easily remove dashes from any value. VBA programming can execute a designated operation in Excel. Here, we will use it to take the hyphens in our data away.

We have already gathered that without the hyphens, our target data will become numbers and will lose any leading zeros. Hence, the data will be formatted to text and then have the dashes exchanged with blank text.

Follow these steps to utilize VBA programming for removing dashes from values in Excel:

• Head to the Developer tab and click on the Visual Basic icon in the Code If you find that your tab set is missing the Developer tab, you can customize the Ribbon to include it. Alternately, use the Alt + F11 keys.
• 
• The Visual Basic editor should now be open.
• Click on the Insert tab in the toolbar and select Module from the drop menu.
• 
• Enter the code below in the Module window by copy-pasting.

• Sub RemoveDashes()
  Dim R As Range
  Dim W As Range
  On Error Resume Next
  xTitleId = "Excel"
  Set W = Application.Selection
  Set W = Application.InputBox("Range", xTitleId, W.Address, Type:=8)
  Application.ScreenUpdating = False
  For Each R In W
   R.NumberFormat = "@"
   R.Value = VBA.Replace(R.Value, "-", "")
  Next
  Application.ScreenUpdating = True
  End Sub

  The code will lead to a pop-up window for selecting the range. The values in those cells will be converted to text format after which the dashes will be replaced with blank text with the Replace function in VBA. If no hyphens are found in a cell, which means the loop hits an error, the code will resume the loop for the next cells.

• Run the code using the Run button in the toolbar or by hitting the F5
• In the pop-up window, enter the target range or select the range on the sheet and it will be entered automatically.
• When done, hit the OK
• 
• The task will have been carried out on the sheet in the background.
• Close the Visual Basic editor to return to the worksheet.
• The values in the selected range will be clear of dashes and in text format:
• The Number Format bar and the errors displayed both confirm that the numbers are stored as text. To rid the dataset of these consecutive green little triangles, we will ignore the error. No, not literally. This is how:
• Select all the cells showing the error.
• Click the error icon showing at the top-left of the data and select the Ignore Error

The numbers will remain in the text format and the error notification will be dealt with:

And that's how you effectively remove dashes in Excel. Excel problems may not be dashing in the least but bit by bit you can learn to have excellent command over them. That's the journey we want to keep you on. Ready? Tricky? Go!