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.
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]
- 7+ Methods to Remove Characters from Left in Excel
- 5 Approaches to Remove Characters from the Right in Excel
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
- 6 Ways to Remove the First 2 Characters in Excel
- 6 Ways to Remove the First 4 Characters 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
![[2 Cases] Count Columns in Excel Until a Certain Value Reached](https://excelgraduate.com/wp-content/uploads/2022/06/excel-count-columns-until-value-reached-3.png)
