Editor choice

How to Remove Dashes from Phone Number in Excel

Get FREE Advanced Excel Exercises with Solutions!

When using Excel, we often need to manage phone numbers for tasks such as managing contacts, conducting market analysis, or handling customer information. However, phone numbers stored with unnecessary dashes can cause problems such as data disruption, slow down automated tasks, and hinder communication between systems. Fixing this issue is essential for efficient and accurate data management.

In this Excel tutorial, I will show you some efficient methods to remove dashes from phone numbers in Excel using the Find and Replace feature, the Format Cell feature, the SUBSTITUTE function, and other methods.

For illustration, I have taken a dataset containing a list of customer IDs, names, and phone numbers with dashes. Our goal is to remove the dashes from the phone numbers.


Why Do Dashes Appear in Phone Numbers in Excel?

Dashes often end up in Excel phone numbers due to various reasons. Some of them are listed below:

  • Manual Entry: People type phone numbers differently, and some include dashes.
  • Imported Data: When bringing in data from other sources, like databases or files, the formatting may vary, leading to dashes.
  • Copy-Paste Issues: If you copy phone numbers from different places, the dashes might tag along, causing inconsistencies.
  • Different Platforms: Data from various platforms may have diverse formats, contributing to the presence of dashes in Excel phone numbers.

6 Ways to Remove Dashes from Phone Number in Excel

In this section, I will show you six easy ways to remove dashes from phone numbers in Excel. Here I will use the Find and Replace feature, Format Cell feature, Flash Fill feature, SUBSTITUTE function, VBA macro, and Pivot Table. Each method has its own merits and demerits. Let’s explore the methods one by one:


Applying Find and Replace Feature

To remove dashes from phone numbers using the Find & Replace feature, follow the steps below:

  1. First, select the range of cells where the phone numbers are located.
  2. Then, go to the Home tab > Find & Select dropdown > Replace.
    Apply Find & Select Feature to Remove Dashes from Phone NumbersAs a result, the Find and Replace dialogue box will open.
    Note: You can press CTRL + H to bring out the Find and Replace dialogue box.
  3. In the Find and Replace dialog box:
    • In the Find What field, type Dash/Hyphen (-)
    • Leave the Replace With field Empty / Null ( )
    • Click on the Replace All option.

    As a result, a message box will be displayed, informing users of the number of replacements made.

Here, we can see the results where all the dashes were removed from the phone numbers.

Read More: How to Remove Dashes from SSN in Excel


Using Format Cell Feature

To customize the appearance of your data in a worksheet, you can use the Format Cell feature. In this method, you will see how to remove dashes from phone numbers using a custom format. Follow the steps below to learn how to use the Format Cell feature to remove dashes from phone numbers:

  1. Select the range of cells from where you want to remove the dashes.
  2. Now, go to the Home tab > Number group > Dialogue Box Launcher Icon.
    Clicking The Dialogue Box LauncherAs a result, the Format Cells dialogue box will open up.
    Note: You can use the Ctrl+1 shortcut key to open the Format Cells dialogue box.
  3. Now, in the Format Cells dialogue box:
    • Go to the Number tab > Custom Category.
    • Pick 00000000000 from the available types under the Type.
    • Click on OK.
      Use Format Cell Feature to Remove Dashes from Phone Numbers

As a result, the dashes will be removed from the phone numbers.

Read More: How to Remove Non-Alphanumeric Characters in Excel


Applying SUBSTITUTE Function

If you want to keep the original phone numbers with the dashes but remove the dashes and place them elsewhere, you can use the SUBSTITUTE function. To remove dashes from phone numbers in Excel using the SUBSTITUTE function, follow the steps below:

  1. Select a blank cell and type the following formula: =SUBSTITUTE(D5,"-","")
    where the D5 is the cell containing a phone number with dashes.
  2. Now, press ENTER.
    Consequently, it will result in the phone number without the dashes.
    Applying SUBSTITUTE Function to Remove Dashes
  3. Now, drag the Fill Handle to autofill the rest of the cells.
    Using Fill Handle to Autofill Cells

The above formula eliminates all dashes from phone numbers.

Note: If you only want to remove a specific instance of dash, you can use the following formula: =SUBSTITUTE(D5,"-","", N)
Replace N with the instance number of the dash you want to remove. For example, to remove the 2nd dash, use the formula: =SUBSTITUTE(D5,"-","",2)


Using Flash Fill Feature

If you’re using Excel 2013 or a newer version, you can utilize the Flash Fill feature to remove dashes from phone numbers more efficiently compared to using the SUBSTITUTE function. However, it’s important to note that the formatting of the phone numbers must be uniform for this method to work correctly. In other words, the position of the dashes must always be the same for all phone numbers.

To use the Flash Fill feature for removing the dashes from the phone numbers, follow the steps below:

  1. Click on a cell adjacent to the first cell of the column containing the phone numbers.
  2. Now, manually type the first phone number without the dashes.
  3. Now, while selecting the first cell, press Ctrl+E to flash-fill the rest of the cells.

    Note: To launch the Flash Fill feature, you can also go to the Home tab > Editing group > Fill drop-down > Flash Fill.

As a result, you will also get the rest of the phone numbers without the dashes.


Running VBA Macro

If you need to remove dashes from a range of cells frequently, using a VBA macro is the most efficient way. Here, I have developed a VBA code that removes the dashes from a selected range of cells. To remove dashes using VBA, follow the steps below:

  1. Press ALT+F11 altogether to open Microsoft Visual Basic.
  2. Next, click on Module from the Insert tab.
    Use VBA Macro to Remove Dashes from Phone NumbersAs a result, a new module will open up.
  3. Paste the following code in the module window:
    Sub DeleteDashes()
    Dim rng As Range
    Dim WorkRng As Range
    On Error Resume Next
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    Application.ScreenUpdating = False
    For Each rng In WorkRng
    rng.NumberFormat = "@"
    rng.Value = VBA.Replace(rng.Value, "-", "")
    Next
    Application.ScreenUpdating = True
    End Sub
  4. Now, click on Run to run the code. Alternatively, you can press F5.
    Consequently, an InputBox will pop up.
  5. In the InputBox :
    • Select the range of cells from which you want to remove the dashes.
    • Click on OK.

As a result, the dashes will be removed from the phone numbers.

Use VBA Macro to Remove Dashes from Phone Numbers

If you need to run the code frequently, you can assign a shortcut key to the macro to run it directly from the worksheet.


Using Power Query

If you need to import phone numbers into your working worksheet from an external source or another worksheet using Power Query, you can format them and remove any unnecessary dashes directly in the Power Query window before importing them. Follow the steps below to remove the dashes from phone numbers using the Power Query:

  1. To import data, go to the Data tab > Get & Transform Data group > From Table/Range.
    As a result, an InputBox named Create Table will open up.
    Note: As I will import the data from a range in my worksheet, I choose the From Table/Range option. However, you need to select an option according to your desired source type.
  2. Now, in the Create Table InputBox, select the data source range and click OK.
    Importing Data from TableAs a result, the Power Query window will open up.
  3. Now, in the Power Query window:
    • Select the column containing phone numbers with dashes.
    • Right-click on it to open the context menu
    • Click on Replace Values in the context menu.
      Replacing Values in Power Query WindowAs a result, the Replace Values window will pop up.
    • In the Replace Values window:
      • Type the dash symbol (-) in the Value to Find field.
      • Leave the Replace With field empty.
      • Click OK.

      Replace Value WindowAs a result, the dashes will be removed from the phone numbers.
      After Replacing the Dashes from the Phone Number in Power Query

  4. Now, click the Close & Load option to load the phone numbers without dashes in a new worksheet.
    Closing And Loading to A New Worksheet

As a result, the phone numbers without dashes will be stored in a new worksheet like this below.
-Imported Dataset from the Pivot Table


Download Practice Workbook


Conclusion

In this article, I have shared some efficient methods to remove dashes from phone numbers in Excel. I have discussed their advantages, disadvantages, and situations where they are suitable to use. I hope these methods will prove useful and make your work in Excel smoother. If you have any further questions or concerns, feel free to share them in the comment section. Thank you and goodbye!


Frequently Asked Question

How do I remove special characters from a phone number in Excel?

To remove a special character from a phone number in Excel, follow the steps below:

  1. Select the range of cells containing phone numbers with special characters.
  2. Press Ctrl + H to open the Find and Replace dialog.
  3. In the Find What box, type the character.
  4. Leave the Replace With box blank.
  5. Click Replace All.

As a result, the special character will be removed from the entire range.

Why is there a dashed line in Excel?

There are several reasons why dashed lines may appear in Excel. One of the most common reasons is to indicate a page break. dashed lines can also appear when the user has enabled dotted borders or when gridlines are turned on.

How Do I Get Rid of GREY Dashed Lines in Excel?

To remove the dashed line in Excel:

  1. Go to the File tab > Options;
    The Excel Options dialog box will open.
  2. In the Excel Options dialog box,
    • Click on the Advanced option in the left pane.
    • Scroll down to the section – “Display options for this worksheet
    • Uncheck the option – “Show page breaks

As a result, the dashed line will be hidden.


Related Articles

<< Go Back To Remove Specific Characters in Excel | Excel Remove Characters | Data Cleaning in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo