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:
| REPLACE(REPLACE(phone_num,n1,0,"-"),n2,0,"-") |
Arguments
| Phone_num: the number you want to add dashes. |
| N1: the first position of number string you want to add dash. |
| N2: the second position of number string you want to add dash. |
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:
| =REPLACE(REPLACE(B3,4,0,"-"),8,0,"-") |
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 fileRelative 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.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
