Are you working on data sets in Excel that require tidying up numbers by removing dashes? Whether it's for formatting addresses, cleaning phone numbers, or preparing data for import, getting rid of those pesky dashes can streamline your work significantly. Here’s how you can easily remove dashes in Excel through several methods, ensuring your data is clean and uniformly formatted.
How to Remove Dashes Using Find and Replace
The simplest way to remove dashes is by using the ‘Find and Replace’ feature:
- Select the cells or range where you want to remove the dashes.
- Press
Ctrl+Hon your keyboard to open the ‘Find and Replace’ dialog box. - In the ‘Find what’ field, type a dash (-).
- Leave the ‘Replace with’ field empty.
- Click ‘Replace All’ to remove all dashes in the selected cells.
🗒 Note: This method can also be used to remove other unwanted characters, not just dashes.
Using Text to Columns
If your data has dashes as separators, the ‘Text to Columns’ feature might be useful:
- Select the column containing the data.
- Go to Data tab > Text to Columns.
- Choose ‘Delimited’ in step 1 of the wizard.
- Select ‘Other’ and enter the dash (-), then click next.
- Choose to ‘Finish’ to apply the changes. If needed, you can then use the ‘Concatenate’ function to combine the split text back together without dashes.
| Method | Best For |
|---|---|
| Find and Replace | Quick bulk editing |
| Text to Columns | Data with consistent separator format |
Removing Dashes with Excel Functions
Excel offers functions like SUBSTITUTE or TEXTJOIN to dynamically remove dashes:
- SUBSTITUTE: =
SUBSTITUTE(A1,“-”,“”)- This removes all dashes from the string in cell A1. - TEXTJOIN: =
TEXTJOIN(“”, TRUE, TEXTSPLIT(A1,“-”))- Split the text by dashes and join back without them.
Using Power Query for Advanced Data Cleaning
For larger datasets or advanced cleaning:
- Select your data range, then go to Data tab > Get Data > From Table/Range.
- In Power Query Editor, use Replace Values under the Home tab to replace “-” with “” (nothing).
- After transformation, load your data back into Excel.
💡 Note: Power Query is particularly useful for regular data cleaning tasks as you can save your steps and apply them to new data.
Using VBA for Custom Solutions
If you need a more tailored solution:
- Open the VBA editor with
Alt+F11. - Insert a new module, and write a VBA function like:
Function RemoveDashes(inputText As String) As String
RemoveDashes = Replace(inputText, “-”, “”)
End Function
=RemoveDashes(A1)⚠️ Note: VBA requires some programming knowledge but provides a high degree of customization.
In summary, removing dashes in Excel can be accomplished with simple methods like Find and Replace for quick edits, or more sophisticated tools like Power Query or VBA for repetitive or large-scale data cleaning tasks. Each approach offers its benefits, allowing you to choose based on your specific needs, data complexity, and the frequency of cleaning tasks. Remember, the key to efficient data management in Excel is understanding the tools at your disposal and how to apply them effectively.
Can I remove dashes from multiple columns at once?
+Yes, by using ‘Find and Replace’ over multiple selected columns or by applying Power Query transformations to an entire dataset, you can remove dashes from all columns simultaneously.
Is there a function to remove dashes?
+Excel has built-in functions like SUBSTITUTE or TEXTJOIN which can help remove dashes from text strings. You can also use custom VBA functions for this purpose.
Do these methods permanently change the data in Excel?
+Using ‘Find and Replace’ or VBA functions will modify the original data. Power Query, however, allows for non-destructive changes, meaning you can revert or modify the transformations at any time before loading data back into Excel.
Related Terms:
- excel remove hyphens from numbers
- remove dashes from excel worksheet
- excel remove hyphen from string
- excel remove first character
- remove dashes from excel cell
