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.
Here, I will be using a dataset including social security numbers or SSNs as raw data. These numbers have multiple Dashes.
Remove Dashes in Excel Using SUBSTITUTE Function
The easiest and most efficient method to remove dashes in Excel is to use the SUBSTITUTE function.
Syntax
SUBSTITUTE(cell,”old_string”,”new_string”)
Formula
=SUBSTITUTE(A2,“-”,””)Formula Explanation
A2 is the cell that contains raw data. Then, “-” is the old_string that you are replacing and “” is the new_string.
To remove dashes in Excel with SUBSTITUTE function, follow these steps below:
- Select the top cell of the Result column.
- Type the formula: =SUBSTITUTE(A2,“-”,””)
So, this formula will substitute all the dashes(-) of the data of the A2 cell with a null or blank space. - Press ENTER key.
- 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.
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
Remove Dashes 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. Here’s how to remove dashes with Find and Replace Command:
- Select the whole dataset.
- In the Home tab you will find Find & Select. Click on that and choose Replace.
- A pop-up box (Find and Replace) will appear. In the Find what box, put “-”.
- In the Replace with box, put nothing. (as we want to remove the dash).
- At the bottom left of the box there is Replace All button. Then, press it.
This will remove all the dashes from that column. Basically, it will replace all the dashes with space.
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.
Clear Dashes in Excel Using Keyboard Shortcut Key
You can use a keyboard shortcut for Find and Replace command for more efficiency. To clear dashes in Excel, go through these steps below:
- Select the dataset.
- Press CTRL + H key. The find and Replace box will open up.
- In the Find what box, put “-”.
- In the Replace with box, put nothing because we are going to remove dashes.
- Press the Replace All button at the bottom left corner of the box.
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 with SUBSTITUTE
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”)Formula
=SUBSTITUTE(A2,“-”,””)Formula Explanation
A2 is the cell that contains Raw Data. Then, “-” is the old_string that you are replacing and “” is the new_string.
Steps to remove dashes without removing the zeros:
- Type in the top cell of the Result column. =SUBSTITUTE(A2,“-”,””)
- Press ENTER key.
- 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.
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])Formula
=SUBSTITUTE(A2,“-”,””,2)
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.
To remove the nth dash in Excel:
- Click the top cell of the Result column.
- Copy and paste the formula: =SUBSTITUTE(A2,“-”,””,2)
- Press ENTER key.
This formula will substitute all the 2nd dashes from your data with blank spaces. - 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.
You can see the data without all the dashes in the following image.
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.
Frequently Asked Questions
Why does Excel show dash?
Excel may display a dash for several reasons:
- Empty Cells or Formulas: If a cell is empty or contains a formula that results in an empty value, Excel may display a dash to signify a null or undefined value.
- Custom Formatting: The dash (-) is a common placeholder in custom number formats. If you’ve applied a custom format to cells, it might include a dash as a placeholder for zero or null values.
- Errors or #N/A Values: In some cases, Excel displays a dash to represent errors or #N/A values in cells.
To address this, check the content of the cells, review custom formatting, and resolve any errors or #N/A values in your data. Adjust formatting and content accordingly to ensure accurate representation in Excel.
How do I get rid of dash lines in Excel?
To get rid of dash lines in Excel, you can adjust cell borders. Here’s a concise guide:
- Select the cell or range with dash lines.
- Right-click and choose Format Cells.
- In the Format Cells dialog, go to the Border tab.
- Clear or choose a different border style for the affected cells.
This removes dash lines by modifying the cell borders directly. Adjust the steps based on your specific needs or version of Excel.
How do I remove spaces between dashes in Excel?
To remove spaces between dashes in Excel, you can use the SUBSTITUTE function. Here’s a concise formula: =SUBSTITUTE(cell_reference, ” – “, “-“)
Replace “cell_reference” with the reference to the cell containing the text. This formula substitutes occurrences of a space followed by a dash and another space with just a dash, effectively removing the spaces between dashes. Adjust the cell reference as needed for your specific case.
How do I remove everything after a dash in Excel?
To remove everything after a dash in Excel, use the LEFT and FIND functions. Here’s a concise formula: =LEFT(cell_reference, FIND(“-“, cell_reference & “-“) – 1)
Replace “cell_reference” with the reference to the cell containing the text. This formula extracts the text from the beginning of the cell up to the position just before the first dash, effectively removing everything after the dash. Adjust the cell reference as needed for your specific case.
Related Articles
