Excel Formula: Add dashes to phone numbers

Here is a list of phone numbers in a worksheet, but generally, you rather like to add dashes to separate the phone number for better read as below screenshot shown, how can you use a formula to quickly add dashes to them in Excel? This tutorial will give you an answer.

Generic formula:

Arguments

How this formula work

Here is a phone number in cell B3 which you want to add dashes, please use below formula in a cell:

Press Enter key, the dashes have been added in specified position.

Explanation

REPLACE function is used to find and replace text specified by location in given text with a new text.

The inside REPLACE function REPLACE(B3,4,0,"-") adds dash at the front of the forth character in cell B3.

It returns 313-5512345.

Then the outside REPLACE function can be seen as
=REPLACE("313-5512345",8,0,"-")
Which adds dash at the front of the eighth character in text string 313-5512345.

Sample File

Click to download sample file

Relative Formulas

• Remove First N Characters From Cell
  Here introduces the formula to remove characters from left side of a text string in Excel.
• Trim Text To N Words
  Here introduces the formula to extract n words from left side of a text string.
• Add Comma Between Names
  It provides formulas to add comma between first name, middle name and last name.
• Extract The First Word From Text String In Excel
  This tutorial provides a formula to extract first word from the give text string.

Relative Functions

• FIND function
  Find the position of a character.
• REPLACE function
  Find and replace characters based on given location from text string with a new text.
• TRIM function
  Remove extra spaces from text.
• SUBSTITUTE
  Replace old text with a new one.