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.
|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:
Press Enter key, the dashes have been added in specified position.
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
Which adds dash at the front of the eighth character in text string 313-5512345.
- 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.
- 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.
Replace old text with a new one.