If you’re wondering how to remove dashes in Excel, you’ve come to the right place. Keep reading to know all the effortless methods here!

Data cleaning and restructuring are two indispensable parts of data analytics. In most cases, you won’t find clean data that you can directly feed into data visualizations.

You must restructure the data to remove unwanted characters from it. One such task is removing dashes from numbers or other data in Excel.

In this elaborate Excel tutorial, I present to you various methods to remove dashes in Excel suitable for different sizes of datasets and Excel expertise levels.

Reasons to Remove Dashes in an Excel Dataset

1. Many auto-dialer applications require a clean phone number, like 5556664587. However, leads data you get from customer relationship management tools or third-party leads providers mostly come in hyphen-separated formats, like 555-666-4587. To appropriately format the phone numbers for software, you need to learn the techniques to delete dashes.
2. If there are dashes in numeric figures, you might face problems in applying formulas to those values.
3. When you’re sorting and filtering values in an Excel worksheet, dashes can distort the result.
4. You might face challenges when you need to merge Excel dataset into an external database if there are dashes in your Excel file.
5. When your Excel data analytics relies too much on Data Validation rules, dashes, and hyphens can trigger false validation errors.
6. Unnecessary dashes in chart data can reduce the readability of the visualization to a great extent.
7. You might also want to remove dashes in an Excel dataset to make the dataset consistent throughout the worksheet.
8. Hackers target SSN databases of business by the unique dashed format of showing an SSN. Therefore, businesses often remove these dashes from their customers’ SSN databases to make them look like not SSN databases.

Also read: 7 Ways to Add Chart Axis Labels in Microsoft Excel

How to Remove Dashes in Excel Using Flash Fill

One of the easiest ways to delete dashes between numbers or texts in Excel is by using the Flash Fill method. However, the location of the dashes must be consistent throughout the dataset for the Flash Fill feature to work. Find below the steps you can follow along with your own dataset:

1. Manually type the number without the dashes in a cell adjacent to the source data column.
2. Now, while keeping the first cell selected, press the Ctrl + E keys on the keyboard.

3. Excel will instantly convert the rest of the dashed numbers to non-dashed numbers below the selected cell.

The above method is only suitable for Excel 2013 and later desktop and Excel 2016 and later Mac apps.

How to Remove Dashes in Excel Using a Function

Another intuitive way to remove dashes from a list of numbers or text strings is by using the SUBSTITUTE function. Here’s how it works:

1. Suppose, you got a column of phone numbers with dashes in column B.
2. Select C2 and enter the following formula into the cell:

=SUBSTITUTE(B2,"-","")

3. Hit Enter to get the dash-less phone number in C2.

4. Now, drag the fill handle until the end of the column to format the remaining phone numbers the same way.

The SUBSTITUTE function is available in the Excel PC app since Excel 2007, the Excel Mac app since Excel for Mac 2011, and also in the Excel for web app. So, if you’re unable to use the Flash Fill technique, you can surely use the SUBSTITUTE function.

Remove the nth Dash

Suppose, you don’t want to remove all the dashes but the second one in the phone number list. Here’s how you can do that:

1. Instead of entering the previous formula, enter the following into C2:

=SUBSTITUTE(B2,"-","",2)

2. Hit Enter and Excel will remove the second dash from the phone numbers.

Similarly, edit the formula according to your dataset. For example, if there are five dashes in a number and you need to remove the third one, the formula would be as below:

=SUBSTITUTE(B2,"-","",3)

Get Rid of Dashes in Excel Using Power Query

Power Query is a great tool that enables effortless data transformation and cleaning before importing to the worksheet. So, you can also remove dashes from numbers and text strings in Power Query easily.

Remove Dashes Using Replace Values

1. Import your dataset to the Power Query tool from an external source or another Excel worksheet or Excel file.
2. Select the column where you got the dashed numbers, such as phone numbers.
3. Right-click on the column.
4. Click on Replace Values in the context menu.

If you’re new to the Power Query tool, read this article to learn everything your need to know including how to import external or internal databases to Power Query:

The Complete Guide to Power Query

Now, you should see the Replace Values wizard on your screen. From here, follow along with these steps:

1. Into the Value to Find field, type a dash or hyphen.
2. Keep the Replace With field empty.
3. Click OK to apply the changes.

4. Power Query will instantly remove all the dashes from the column of phone numbers.

When this data transformation process is complete, do these to import the clean dataset to your Excel worksheet

1. Click the File menu on the Power Query ribbon.
2. Select the Close & Load To option.

3. On the Import Data dialog box, click the Existing worksheet selection.
4. Also, highlight a destination cell for data import.
5. Click OK to complete the data import process from Power Query to Excel.

The image above shows the final imported values from Power Query.

Remove Dashes Using Text.Select Function

You need to use the Text.Select function in a custom column in Power Query to get rid of dashes from numbers or text strings in Excel. Here’s how:

1. Load your dataset to Power Query either from external or internal sources.
2. Click the Add Column tab.
3. In the General block, click Custom Column.
4. On the Custom Column dialog box, enter a name in the New column name field.
5. Copy and paste the following formula into the Custom column formula field:

Text.Select([Phone],{"0".."9"}))

6. Click OK to add the new column with numbers without the dashes.

Now, you can import this column to your worksheet by following the Close & Load To method explained earlier.

The custom column will look like the above image when imported into an Excel worksheet.

In the formula for the custom column, modify the part [Phone] according to the column header of your source dataset.

Get Rid of Dashes in Excel Using a VBA Script

If you’re looking for ways to remove dashes from SSNs in Excel or for phone numbers or any other data, you can use Excel VBA to automate the process. That too for thousands of entries. Find below the steps along with the script you must use:

1. Press Alt + F11 to call the Excel VBA Editor tool.
2. Click the Insert menu on the toolbar.
3. Choose Module from the context menu.
4. Now, copy and paste the following script into the blank module:

Sub RemoveDashes()
    Dim rng As Range
    Dim cell As Range
    Dim newData As String
    
    Set rng = Range("B2:B6")
    
    For Each cell In rng
        newData = Replace(cell.Value, "-", "")
        cell.Offset(0, 1).Value = newData
    Next cell
    
    Range("C2:C6").AutoFill Destination:=Range("C2:C6"), Type:=xlFillDefault
    
    Columns("C:C").EntireColumn.AutoFit
End Sub

5. Click the Save button.
6. Click the close (X) button to exit the VBA Editor.

Now, you must run the code. Follow along with the instructions below:

1. Bring up the Macro dialog box by pressing Alt + F8.
2. Select the RemoveDashes macro.
3. Hit the Run button to execute the VBA macro.
4. Excel will automatically remove the dashes and populate the result in column C.

Here’s how you can modify the VBA script so it works in your worksheet:

• B2:B6 is the source data range, so modify it according to your dataset.
• C2:C6 is the destination of the values after removing the dashes. So, replace all the instances of cell range C2:C6 according to the destination cell range you want.
• If your destination column is different than column C, you also need to change Columns("C:C") part of the code. Suppose, the destination is column D, then Columns("C:C") should be edited as Columns("D:D").

How to Remove Dashes in Excel Using Office Scripts

Excel VBA won’t work on Excel for the web app. So, if you wish to automatically remove dashes from thousands of rows of numbers and strings in Excel online, you can use Office Scripts. A good thing is The feature also works on the Excel desktop app. Here’s how:

1. Click the Automate tab on the Excel ribbon.
2. Select the New Script button inside the Scripting Tools block.
3. Copy and paste the following script inside the Code Editor panel on the right.

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range C2 on selectedSheet
	selectedSheet.getRange("C2").setFormulaLocal("=SUBSTITUTE(B2,\"-\",\"\")");
	// Auto fill range
	selectedSheet.getRange("C2").autoFill("C2:C7", ExcelScript.AutoFillType.fillDefault);
}

4. Click the Save script button.
5. Now, hit the Run button.
6. Excel Automate will remove all the dashes from the selected source and populate dash-less values in column C.

Modifying the above Office Scripts code is really effortless. Here’s how:

• Replace B2 with the cell address for the first source data in your worksheet.
• Replace C2 with the first destination cell for the converted values.
• If you’re applying the Auto Fill action from D2 to D100, the C2:C7 code element will become D2:D100.

The first script is the automation of the SUBSTITUTE function that I discussed at the beginning of the article. Find below another Office Scripts code based on the Flash Fill method:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range C2 on selectedSheet
	selectedSheet.getRange("C2").setValue("8956587458");
	// Flash fill on selectedSheet
	selectedSheet.getRange("C2").flashFill();
}

How to Remove Dashed Lines in Excel

Dashed lines in Excel can appear due to the following:

1. Someone added dashed lines as cell borders.
2. You clicked any buttons in the Page Layout tab’s Page Setup commands block.
3. You activated the Page Break Preview and Page Layout buttons in the Workbook Views of the View tab.

For the second and third reasons, you must save the Excel workbook. Then, close it. Open the workbook again and you shouldn’t see the dashed lines anymore.

For the first reason of dashed lines, follow along with these steps:

1. Select the entire table or dataset that shows the dashed lines around a cell selection area.
2. Click the Borders drop-down menu inside the Font block of the Home tab.
3. Choose No Border from the context menu.

Conclusion

So, now you know how to remove dashes in Excel using various methods like a formula, the Format Cell tool, the Power Query tool, Excel VBA, and Office Scripts.

Depending on the dataset’s size and complexities, you can choose a specific method from the above-listed techniques. When the dataset is small and you don’t want to use scripting, you can stick to the functions or the Format Cells tool.

Contrarily, if you’re importing a large dataset containing thousands of dashes, get rid of those on Power Query, the tool you use to inject raw data into Excel from an external database.

Finally, if you’re okay with a little bit of coding in Excel and need to automate the whole process, use the Excel VBA and Office Scripts-based methods.

Don’t forget to comment below to share your experience and the method you liked the most.