How to remove all dashes/slashes/ hyphens from dates in Excel?
Excel commonly displays dates using separators such as slashes or dashes, for example “8/15/2019” or “8-15-2019”. In various business or data processing scenarios, you might be asked to remove these separators from dates, resulting in a continuous string like “08152019”. This may be necessary for importing into other systems, for better sorting, or when complying with a specific data format. Working with dates without separators can help streamline certain types of analysis and reporting where uniformity or compactness of date formats is critical.
What’s the best way to remove the dashes (“-”), slashes (“/”), or hyphens from date values in Excel? This article introduces several practical solutions. Each method is suitable for different use cases, depending on whether you want the changes in a new column, need to handle very large datasets, deal with non-standard text dates, or require automation.
- Remove all dashes/slashes/ hyphens from dates with TEXT function
- Remove all dashes/slashes/ hyphens from dates with Format Cells feature
- Remove all dashes/slashes/ hyphens from text-formatted dates with Substitute function
Remove all dashes/slashes/ hyphens from dates with TEXT function
You can use Excel’s TEXT function to generate date values without separators. This method works by displaying the date as a text string according to the format you specify. It's particularly helpful when you want the result as a new value for further use without changing the underlying data format in the source cells.
Note: This approach outputs the result as text in a separate cell. If you wish to adjust the date format directly within the original cells and keep the “Date” data type rather than text, use the Format Cells method below.
To proceed, select a blank cell in which you want to display the date without any dashes, slashes, or hyphens. Enter the following formula and drag the AutoFill handle down to apply the operation to the rest of your range as needed.
=TEXT(B3,"mmddyyyy")
In the above formula:
- B3 is the cell containing your date value.
- "mmddyyyy" is the specified output style. You can customize this to “ddmmyyyy”, “yyyymmdd”, etc., as needed for your context.
Tips: After applying the formula, copy the results and use Paste Special > Values if you want to overwrite or export plain text results. Be aware that the result is text and will not behave as a date in calculations.
Precautions: Make sure your source data in B3 is a valid Excel date value. Non-date text may produce unexpected results.
Remove all dashes/slashes/ hyphens from dates with Format Cells feature
If you want to remove separators directly from your original date cells and retain the “Date” number format (allowing for further date calculations, sorting, and filtering), the Format Cells feature is ideal. This changes only how the date is displayed, not its underlying value, so functionality and compatibility for further analysis are preserved.
1. Select the date cells from which you wish to remove the dashes, slashes, or hyphens. Right-click and choose Format Cells from the context menu.
2. In the Format Cells dialog box, on the Number tab, choose Custom from the Category list. Then type the desired date code (such as mmddyyyy) into the Type field and click OK. (See screenshot below.)
Note: The “mmddyyyy” format removes all dashes, slashes, and hyphens from the date display. You may adjust the format string to fit your needs, e.g., “ddmmyyyy”, “yyyymmdd”, etc. Make sure you select the “Custom” category to enter a fully custom display code.
Once completed, your selected date cells will display without any separators.
Important: This feature only changes the cell's appearance, not the underlying value. Calculations, charting, and filtering will continue to treat the value as a date. When exporting or copying the data, the displayed format will be retained.
Scenarios and Limitations: Best used when you want a clean visual output without modifying data structure, or when working with a data set that must remain compatible with other date-based formulas. Not applicable if your source dates are stored as non-standard text.
Remove all dashes/slashes/ hyphens from text-formatted dates with Substitute function
Sometimes, date values may be imported from an external database, CSV file, or other software in a text format that Excel does not automatically recognize as dates. In these cases, formulas like TEXT or formatting will not apply, and you need to manipulate the text string directly. The SUBSTITUTE function can quickly remove multiple kinds of separators in text-style dates.
To remove dashes, slashes, or even en dash symbols (“–”) from a text-formatted date string in cell A1, use the following formula:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"/",""),"–","")After entering this formula in a blank cell, press Enter to confirm it. Drag the formula down to apply it to additional cells in your column as needed.
Explanation:
- This formula replaces all occurrences of “-”, “/”, or “–” (en dash) with nothing, effectively removing these characters from the text string.
- If your source cell uses a different separator, adapt the formula by nesting more SUBSTITUTE functions or editing the existing ones.
- The result will be a plain text value without any separators.
Tips: Useful when you have inconsistent or imported date data that is not recognized as true date values by Excel. For large batches, this formula can be filled down a column and results converted to values as needed.
Related articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!