3+ Ways to Remove Dashes in Excel

When you are working in Excel, sometimes data have dashes or hyphens(-). Very often, you need to remove those dashes to process data. There are several ways by which you can remove dashes or hyphens(-) from an Excel worksheet. Here, I’m going to show you more than 3 easy methods to remove dashes in Excel.

Removing Dashes in Excel.xlsx

Table of Contents

Introduction to the Dataset

Here, I will be using a dataset including social security numbers or SSNs as raw data. These numbers have multiple Dashes.

Easiest Method to Remove Dashes in Excel

The easiest and most efficient method to remove dashes in Excel is to use the SUBSTITUTE function.

Syntax

SUBSTITUTE(cell,”old_string”,”new_string”)

Usage Guide

Step_1: Type

 =SUBSTITUTE(A2,“-”,””)

in the top cell of the Result column.

Formula Explanation

A2 is the cell that contains raw data.
“-” is the old_string that you are replacing.

“” is the new_string.

So, this formula will substitute all the dashes(-) of the data of the A2 cell with a null or blank space.

Step_2: Press ENTER key.

Step_3: Now click on the Fill Handle icon and drag it down to your desired range to fill the rest of the cells of that column.

Final Result

All the dashes are replaced with null values. Thus, all the unwanted dashes are removed now as you wanted.

Remove Non-Printable Characters in Excel [5+ Methods]

Alternative Method #1: Using Find and Replace Command

Find and Replace is one the most common command in Excel.

Now, I am going to discuss how you can use it to remove dashes from your data.

Usage Guide

Step_1: Select the whole dataset.

Step_2: In the Home tab you will find Find & Select. Click on that and choose Replace.

Step_3: A pop-up box (Find and Replace) will appear. In the Find what box, put “-”.

Step_4: In the Replace with box put nothing. (as we want to remove the dash).

Step_5: At the bottom left of the box there is Replace All button. Press it.

This will remove all the dashes from that column. Basically, it will replace all the dashes with a hyphen.

Final Result

But, you have to keep in mind that this will also remove any zeros that come at first. For example, it will change 006-82-338 to 682338.

Because here, Excel considers the data as numbers. As zeros at first have no significance, it removes them.

Alternative Method #2: Use Keyboard Shortcut CTRL+H

You can use a keyboard shortcut for Find and Replace command for more efficiency.

Usage Guide

Step_1: Select the dataset.

Step_2: Press CTRL + H key. The find and Replace box will open up.

Step_3: In the Find what box, put “-”.

Step_4: In the Replace with box, put nothing because we are going to remove dashes.

Step_5: Press the Replace All button at the bottom left corner of the box.

Final result

The final result with all the dashes removed will look like this.

4 Ways to Remove First Word in Excel

Remove Dashes in Excel without Removing Zeros

To remove dashes in Excel without removing the zeros, you can use a formula using the SUBSTITUTE function. This considers your data as a string instead of a number. And keeps the zero as it is.

Syntax

=SUBSTITUE(cell,”old_string”,”new_string”)

Usage Guide

Step_1: Type

 =SUBSTITUTE(A2,“-”,””)

in the top cell of the Result column.

Formula Explanation

A2 is the cell that contains Raw Data.

“-” is the old_string that you are replacing.
“” is the new_string.

Step_2: Press ENTER key.

Step_3: Now click on the Fill Handle icon and drag it down to your desired range to fill the rest of the cells of that column.

Final Result

All the dashes are replaced with blank spaces. So, all the unwanted dashes are removed now as you wanted.

How to Remove nth Dash in Excel

Sometimes, you need to remove dashes of nth occurrences. Let’s look at an example.

Suppose your data is 234-56-789 and you want to remove only the second dash keeping the first one as it is. Here the formula I’ve stated above won’t work. Modify it a little bit and you are good to go.

Syntax

=SUBSTITUE(cell,”old_string”,”new_string”,[instance_num])

Usage Guide

Step_1: Type

=SUBSTITUTE(A2,“-”,””,2)

in the top cell of the Result column.

Formula Explanation

A2 is the cell that contains Raw Data.
“-” is the old_string that you are replacing.
“” is the new_string.

2 is the instance number. It will indicate the dashes that come in second.

Step_2: Press ENTER key.

This formula will substitute all the 2nd dashes from your data with blank spaces.

Step_3: Now Click on the Fill Handle Icon and drag it down to your desired range to fill the rest of the cells of that column.

Final Result

Data without all the dashes will look like this,

Conclusion

Here, I tried to discuss how can you remove dashes in Excel and pointed out some methods. The methods are both formula-based and command-based. So you can use any of them according to your choice. Hope this article helps you learn to Excel better. Thank you.

Related Articles