How to Format Phone Number with Dashes in Excel (2 Ways)

As different parts of a phone number bear different meanings, such as country codes and area codes, using dashes can separate the sections and make them more readable and visually appealing. However, in Excel, phone numbers are not automatically recognized, and they’re treated like regular numbers. So, we need to manually add dashes for clarity and proper formatting.

In this Excel tutorial, you will learn how to format phone numbers with dashes in Excel. I will show you how to achieve this using multiple Excel formulas and the Format Cells feature. I will also explain their advantages, disadvantages, and use cases.

In the following image, I showed how phone numbers would be displayed in Excel before and after formatting with dashes.

2 Ways to Format Phone Number with Dashes in Excel

In this section, I will illustrate how to format a phone number with dashes in Excel by categorizing the methods into two broad categories. In the first type, I will show how to use the Format Cells dialog box to format phone numbers with dashes. In the 2nd type, I will describe how to use Excel functions to add dashes in the phone number for formatting. So, let’s explore the methods one by one.

1. Using Format Cells Option

In this section, I’ll show two methods that modify only the visual presentation of the phone numbers without affecting their actual values. I will do it using the Format Cell option while working with the same dataset as I showed in the introduction. Let’s explore the methods below.

1.1 Using Special Category

Excel has some built-in number formats which you can access from the Format Cells window. Among the options in the Special category, there is also a Phone Number format which you can use to format your phone numbers with dashes.

To use the Special category in the Format Cells dialog box to format phone numbers with dashes, follow the steps below:

1. Select the cells containing phone numbers that you want to format.
2. Then go to the Home tab > Number group > Dialog Box Launcher.
   Alternatively, click on the Ctrl+1 shortcut key to open the Format Cells dialog box.
   Consequently, the Format Cells dialog box will appear.
3. In the Format Cells dialog box:
   • Go to the Number tab;
   • Under Category, click on Special;
   • Under the Locale(location), select your location from the available drop-down options;
     As a result, the available format types for that location will be displayed under the Type menu;
   • Under the Type, select the format that has dashes;
     You can preview them under “Sample” to help you choose.
   • Then press OK.

   

As a result, the phone numbers will be formatted according to your chosen format.

For illustration, I have chosen English (United States) as the Locale(location) and Phone Number as the format type. In this format, the first three digits which denote the area code are enclosed by parentheses, and the rest of the digits are separated by a dash. In your case, you need to select the parameters accordingly.

Read More: How to Remove Parentheses from Phone Numbers in Excel

1.2 Using Custom Number Format

If you don’t find your Locale(location) or the desired special format in the Format Cells dialog box, then you can create your own custom format.

To use the custom format to format phone numbers with dashes, follow the steps below:

1. Select the range of cells that contain phone numbers that you want to format.
2. Press Ctrl+1 to open the Format Cells dialog box.
3. In the Format Cells dialog box:
   • Go to the Number tab;
   • Under Category, click on Custom;
   • In the Type field, write the following number format code:
     ###-###-####
Here, each # represents a digit. So adjust the number of # according to your phone number’s digits.
   • Then press OK.

   

As a result, the phone numbers will be formatted with dashes.

A good thing about using this method is that only the appearance of the phone numbers gets changed, but the values of the phone numbers are unaltered. To verify this, you can select any formatted cell and see the value in the formula box.

Read More: How to Write Phone Number in Excel

2. Using Excel Functions

We can also format the phone numbers using various types of formulas like REPLACE, TEXT, and different types of string functions like LEFT, LEN, RIGHT, MID, etc. The formulas keep the original numbers intact and store the formatted phone numbers in a new location. All these methods are explained with examples below.

2.1 Using Replace Function

The REPLACE function replaces part of a text string, based on the number of characters you specify, with a different text string.

To format phone numbers with dashes using the REPLACE function, follow the steps below:

1. First, select a blank cell and enter the following formula:
   =REPLACE(REPLACE(B5,4,0,"-"),8,0,"-")
   Here, cell B5 indicates the first cell of the dataset containing the phone number. Replace cell B5 with your dataset’s first cell.
2. Press Enter.
   Consequently, the destination cell will contain the formatted number with a dash.
   In my case, I wanted to insert two dashes after the 3rd and 6th character, hence in the formula, the inner REPLACE function adds a dash after the 3rd character by starting at the 4th position, and the outer REPLACE function, building on the modified text, adds another dash after the 6th character by starting at the 8th position.
3.  Now, use the Fill handle icon in the corner of the cell and drag it down to the end of the dataset.
   

As a result, all the phone numbers will be formatted with dashes.

Note: In this formula, if you wish to include additional dashes, you can achieve that by adding more instances of the REPLACE function. On the other hand, if you want to adjust the positions where the dashes are inserted, you simply need to modify the second argument within each REPLACE function. Essentially, the second argument determines the starting position for inserting the dashes, allowing you to easily customize their placement in the text.

2.2 Using TEXT Function

By using the TEXT function, users can specify in which format the content of a cell will be displayed. Hence, you can also use it to format phone numbers with dashes.

To know how to format phone numbers with dashes with the help of the TEXT function, follow the steps below:

1. First, select a blank cell and write the following formula
   =TEXT(B5,"???-???-????")
Here, replace B5 with the first cell of the unformatted phone number column.
2. Press the ENTER button.
   Consequently, you will see a formatted phone number.
   
3. Now, drag the Fill Handle button in the corner of the cell down to the end of the dataset.

As a result, all the phone numbers will be formatted with dashes like this.

Note: In the 2nd argument of the TEXT function, each “?” represents a character. Here, you can add more “?” or adjust the position of the dashes according to the phone numbers of your dataset.

2.3 Using LEFT, MID, and RIGHT Functions

By using various string functions like LEFT, MID, and RIGHT, the users can extract certain parts of a text and piece them together with the help of the Ampersand operator (&) after inserting dashes in the desired positions.

To know how to use the LEFT, MID, and RIGHT functions to format phone numbers with dashes in Excel, follow the steps below:

1. First, select a blank cell and write the following formula
   =LEFT(B5,3)&"-"&MID(B5,4,3)&"-"&RIGHT(B5,4)
Here, replace B5 with the first cell of the unformatted phone number column.
2. Press the ENTER button.
   Consequently, you will see a formatted phone number.
   In the formula, the LEFT(B5,3) extracts the first three characters, followed by a dash, then MID(B5,4,3) extracts the middle three characters starting from the 4th position with another dash, and finally, RIGHT(B5,4) captures the last four characters.
3. Now, drag the Fill Handle button in the corner of the cell down to the end of the dataset.

As a result, all the phone numbers will be formatted with dashes like this.

Note: The formula that I provided above works only for 10-digit numbers and it only adds dashes after the 3rd and 6th character. For your dataset, you need to change them accordingly. For example, If you have 12 digits phone number and you want to format it in ###-####-##### format, you need to modify the formula in the following way:
=LEFT(B5,3)&"-"&MID(B5,4,4)&"-"&RIGHT(B5,5)

Read More: Excel Formula to Change Phone Number Format

Download Practice Workbook

Download this practice workbook from the link below.

.

Conclusion

In this article, I demonstrated two ways to add dashes to phone numbers: one using Excel formulas and the other through the Format Cells option. With the formula method, I stored the newly formatted numbers in a different place while keeping the originals intact. On the other hand, using Format Cells directly changes the appearance of the actual phone numbers. This method offers flexibility, allowing us to create custom formats as needed. I trust these methods prove helpful for you. If you have any questions, don’t hesitate to ask in the comments.

Frequently Asked Questions

What Is the Advantage of Using Format Cells Over Formulas?

Using Format Cells directly modifies the visual appearance of phone numbers without requiring additional cells for storage. It offers a convenient way to customize formats while keeping the original values intact.

How Do I Revert to the Original Phone Number Format After Formatting With Dashes?

If you’ve used the Format Cells option, you can easily revert to the original format by selecting the cells, right-clicking, choosing “Format Cells,” and selecting “General” under the Number tab.

Related Articles

• How to Format Phone Number with Country Code in Excel
• How to Format Phone Number with Extension in Excel
• Keep 0 Before a Phone Number in Excel
• [Solved!]: Excel Phone Number Format Not Working

<< Go Back to Phone Number Format | Number Format | Learn Excel