Do you need to learn how to remove dashes in Excel? No problem! Here are three easy ways for you to learn how to remove dashes in Excel: the SUBSTITUTE formula, Flash Fill, and the Find and Replace formula.
Read on as I show you how each method works with step-by-step instructions.
Table of Contents
How To Remove Dashes in Excel
I utilize a few easy-to-use methods to remove dashes in Excel. Depending on the way I’ve organized my spreadsheet and the type of data I’ve collected, some of these methods may be more effective than others.
In the next section, I’ll show you in detail how to remove dashes in Excel using three methods: the SUBSTITUTE formula, Flash Fill, and Find and Replace.
Using Excel Formula to Remove Dashes
If you’re already familiar with formulas and functions in Excel, I suggest utilizing the SUBSTITUTE function to remove dashes in Excel.
This method allows you to replace every dash symbol with a space or a different character. Additionally, it lets you choose the specific cells to remove the dashes. Let’s take a look at the example below to give you a better idea of how we can remove dashes from phone numbers in Excel.
First, let me show you how to use the formula and the syntax for the SUBSTITUTE function:
=SUBSTITUTE(txt, old, new, instance)
The formula requires three parameters to work, including:
- txt: This is a required argument. It refers to the text or cell reference that contains the text where you want to substitute the characters.
- old: This parameter contains the text that you want to replace. Therefore, make sure you write the text inside quotation marks ( “ ).
- new: This argument contains the text you want to replace the old text with.
- instance: This is an optional parameter that specifies the occurrence of the old parameter that you want to replace the new text with.
Follow the steps below to learn how to remove dashes from numbers in Excel using the SUBSTITUTE function:
- First, I open Excel and head over to the spreadsheet where I want to remove the dashes function in Excel. I then click on the cell where I want to enter the formula.
- Here, I will enter the initial part of the formula, which is =SUBSTITUTE(.
- Next, I will enter the parameters for the formula. For the first parameter, I will add the cell range containing the phone numbers.
- For the second parameter, I will write “-” telling the formula to substitute the dashes.
- And in the third parameter, I will add two double quotes ( “ ) with nothing in them.
- Note: Make sure to separate the parameters using commas ( , ).
- Finally, I add a closing bracket “ ) ” to finish the formula and press “Enter” to execute the formula.
Removing Dashes in Excel Using Flash Fill
Suppose you’re unfamiliar with formulas in your spreadsheet and have a consistent dataset. In that case, I recommend using the Flash Fill method.
This feature in Microsoft Excel allows me to automatically combine and split data into columns according to the recognized patterns. It is a great method to use when I’m looking to reformat data. Let me demonstrate how to use the Flash Fill method to remove dashes from SSN in Excel.
Follow the steps below to learn how to remove dashes from SSN in Excel:
- I open Excel and head to the spreadsheet where I want to use the Excel Flash Fill feature. I click on the cell where I want to enter the data.
- Now, I copy the contents of the first cell and remove the dashes from it. Then, I paste it into an adjacent cell.
- I suggest pasting it to the right or left of the cell. This will ensure the entire column has no values.
- NOTE: If you do not paste the contents to the left or right, the Flash Fill might not work.
- To activate the Flash Fill function, I then go to the “Home” tab and click the “Fill” button in the “Editing” section.
- There, I select the “Flash Fill” option from the drop-down menu.
This should fill up the empty cells with the expected results. In this case, Flash Fill will remove the dash from SSN in Excel.
Removing Dashes With Find and Replace
Another easy way to remove all the dashes in Excel is to utilize the Find and Replace feature. This feature will replace the dash with a null string, essentially removing it.
Let me show you how this works:
- First, I click and drag my cursor across the cells where I want to remove the dashes.
- Next, I head over to the “Home” tab, click on “Find and Replace,” and then on the “Replace” button in the drop-down menu.
- Alternatively, I can use the Ctrl+F keyboard shortcut to open the Find and Replace dialog box.
- In the “Find and Replace” dialog box, I write the string I want to replace in the “Find what” text box.
- In this case, I enter the dash symbol because I want to remove the dashes.
- I will leave the “Replace with” text box empty.
- Now, I click the “Replace All” button to remove all the dashes.
This will show a prompt showing how many dashes were replaced by the function. Feel free to close the box, and you’re done!
Why Should You Remove Dashes in Excel?
There are many reasons why I may choose to remove dashes in Excel, but one of the most common reasons to do this is to ensure my document is uniform. Often, unnecessary characters like dashes and commas remain when I import data from another source into Excel, such as SSNs or phone numbers.
In some cases, I may also want to remove dashes in Excel spreadsheets if I run calculations with the data. For example, suppose I need to add or subtract the numbers in my spreadsheet. In that case, I may remove the dashes before Microsoft Excel can perform these calculations accurately. Understanding how to remove dashes in Excel can help me create easy-to-understand spreadsheets.
Excel is one of the best spreadsheet software platforms in the market. That’s why many business professionals use it to manage critical information.
If you want to use Excel to create a professional-looking spreadsheet, a good place to start is learning some of the basic functions. This A-Z Excel course has everything you’ll need, from how to use the most basic functions to managing large datasets, creating dynamic reports, and, of course, how to remove dashes in Excel.
Related: 11 Best Excel Courses Online in 2023
Tips to Remove Dashes in Excel
I also have a short video on YouTube that discusses this. I show you three ways to remove hyphens including each method I’ve described in more detail above.
Here are some additional tips that help remove dashes in Excel while staying organized:
- Backup your spreadsheets: I suggest creating regular backups of your spreadsheet in case something goes wrong. This way, you don’t risk losing any of your data, as you can open the backup spreadsheet if you lose your data in the main spreadsheet.
- Change cell format: When I remove dashes from my spreadsheet, I’ve noticed that Excel may alter how it interprets my data and remove zeros from the beginning of each number set. To avoid this, I changed the cell format to display as many digits as I needed.
- Use Ctrl+H shortcut: If I want to use the Find and Replace method to remove dashes from my Excel spreadsheet, I can use the Ctrl + H shortcut to open the replace dialog box.
- Save changes frequently: I can use the Ctrl + S keyboard shortcut to save my Excel spreadsheet frequently. This practice helps me protect my data.
Frequently Asked Questions
How Do I Remove Dashes From Text in Excel?
There are multiple methods to remove dashes in Excel. The easiest way to do this is using the “Find and Replace” method. Open the replace feature by clicking the “Find and Replace” button in the main “Home” tab. Then, type in the dash symbol and click the “Replace All” button.
How Do I Change a Dash to Zero in Excel?
You can use the SUBSTITUTE formula in your Excel worksheet to change a dash to zero. To do this, use the following formula:
=SUBSTITUTE( [your-cell-range], "-", "0").
For this formula, make sure you enter the address for your cell range in the first parameter of the formula.
I have shown you three methods above on how to remove dashes in Excel. They all work equally well, so you just select the easiest and memorize it.
If you’re looking for spreadsheet templates, we have some to check out here. Use code “SSP” to get 50% off all templates from our store.