How to remove hyphens in Excel

You can watch a video tutorial here.

The hyphen serves multiple purposes in Excel. It can be used between words e.g. short-term, as a separator for phone numbers or identification numbers, or as the minus operator when subtracting numbers or to indicate that a number is negative. When you are editing text that contains a hyphen, you may need to remove the hyphens. If you have a set of numbers containing hyphens, the hyphens need to be removed before they can be used in calculations. Phone numbers frequently have hyphens and you may need to remove them to make the data more consistent. 

In Excel, there are 2 ways in which hyphens can be removed. The first way requires the use of the Find & Replace tool and the second way uses the SUBSTITUTE() function.

  1. SUBSTITUTE(): this replaces one piece of text with another piece of text in a text string
    1. Syntax: SUBSTITUTE(text string, old text, new text, instance num)
      1. text string: the text in which the replacement is to be done
      2. old text: the text to be replaced
      3. new text: the replacement text
      4. instance num: (optional) the number of occurrences of the old text to be replaced

Option 1 – Use Find & Replace

Step 1 – Open the Find & Replace box

  • Go to Home > Editing
  • Expand the Find & Select dropdown
  • Select Replace

OR

  • Press Ctrl+H

Step 2 – Enter the details

  • Under Find what: type ‘-‘
  • Leave Replace with: blank
  • Click Replace All

Step 3 – Acknowledge the change

  • Click OK in the confirmation box

Step 4 – Check the result

  • The hyphens are removed 

Option 2 – Use the SUBSTITUTE() function

Step 1 – Create the formula

  • Select the destination cell
  • Type the formula using cell references:

=SUBSTITUTE(Phone number,”-“,””)

  • The double quotes (“”) are used to replace the hyphen with nothing
  • Press Enter

Step 2 – Copy the formula to the other cells

  • Using the fill handle from the first cell, drag the formula to the remaining cells

OR

  1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
  2. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

Step 3 – Check the result

  • The hyphens are removed