How to Fix Excel Text to Columns Not Accepting a Dash Delimiter
Question details
Users are unable to use a dash symbol as a custom delimiter to split data in Excel's Text to Columns wizard.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to split text strings into multiple columns using a dash (-) as the separation character.
- Observed behavior
- Excel rejects the dash in the Text to Columns custom delimiter field, failing to split the data properly.
Before troubleshooting, ensure you are typing a standard minus sign/dash and not an en-dash or em-dash (special characters), as Excel may not recognize formatted typographical dashes during data splitting.
Test in a New Workbook and Restart Excel
A temporary application glitch or workbook-specific corruption can cause Excel to reject custom delimiters. Restarting and testing in a fresh file helps isolate the issue.
Sometimes, the issue is isolated to a specific workbook due to background formatting or cached data errors. Creating a new workbook verifies if the core application is functioning correctly.
Save your current work and close the Microsoft Excel application completely. Reopen Excel from your Start menu or taskbar.
Open a blank workbook and type a sample text string containing a standard dash (e.g., 'Data-123') into cell A1.
Select cell A1, navigate to the 'Data' tab, and click 'Text to Columns'. Choose 'Delimited', click 'Next', check the 'Other' box, and type a dash to see if it is accepted.
Update Microsoft Office to the Latest Version
Outdated Excel installations may contain known bugs that affect data parsing tools. Updating to the latest version resolves these application-level issues.
Use Alternative Data Splitting Methods
If the Text to Columns wizard remains unresponsive to the dash delimiter, you can bypass the wizard entirely using built-in formulas or Power Query.
Split Text by Dash Seamlessly in WPS Spreadsheet
If Microsoft Excel continues to experience glitches with custom delimiters, WPS Spreadsheet provides a highly reliable, free alternative. Its Text to Columns feature handles custom delimiters like dashes effortlessly, allowing you to parse your data without interruptions.
- 1. Open Your Data in WPS Office: Launch WPS Spreadsheet and open the document containing the data you need to split.
- 2. Select the Target Cells: Highlight the column or specific cells containing the text strings separated by dashes.
- 3. Access Text to Columns: Navigate to the 'Data' tab on the top ribbon and click on the 'Text to Columns' button.
- 4. Configure the Delimiter: Choose 'Delimited', click 'Next', check the 'Other' option, and type a dash (-) in the input box.
- 5. Finish Splitting: Click 'Finish' to instantly divide your text into separate columns accurately.
Frequently Asked Questions
Why won't Excel accept my custom dash delimiter?
Excel might reject a custom delimiter due to a temporary application glitch, conflicting regional settings, or workbook corruption. Additionally, if the dash was copied from a word processor, it might be an en-dash or em-dash (special characters) rather than a standard minus sign recognized by Excel.
How can I split text by a dash using formulas instead of Text to Columns?
In newer versions of Excel, you can use the TEXTSPLIT function by typing =TEXTSPLIT(A1, "-"). For older versions without this function, you can combine LEFT, RIGHT, MID, and SEARCH functions to mathematically locate the dash and extract the surrounding text.
Does Power Query support splitting columns by a dash?
Yes, Power Query is an excellent alternative. Simply select your data, go to Data > From Table/Range, and in the Power Query Editor, click on 'Split Column' > 'By Delimiter'. Choose 'Custom' and enter your dash.
