How to Change Excel Text to Columns Default to Delimited
Question details
Users are looking for a way to make the 'Delimited' option the permanent default in Excel's Text to Columns wizard to avoid selecting it manually every time.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Using the Text to Columns wizard to split data strings into separate cells.
- Observed behavior
- The wizard does not remember the 'Delimited' option as a default setting; users must manually choose it upon every new use of the tool.
Since Microsoft Excel does not have a built-in toggle to permanently change the default Text to Columns behavior, you will need to ensure the Developer tab is enabled to use a VBA macro.
Automate Delimited Text to Columns Using a VBA Macro
Because Excel lacks a standard registry edit or setting to save 'Delimited' as the permanent default, creating a VBA macro is the most effective workaround for regular use.
This solution involves writing a short macro that bypasses the manual wizard selection entirely and directly applies a delimited data split to your selected cells.
Press the Alt + F11 keys simultaneously on your keyboard to launch the Visual Basic for Applications (VBA) editor.
In the top menu, click on 'Insert' and select 'Module' from the dropdown list. This will create a blank canvas for your code.
Type out a script using the Selection.TextToColumns command, ensuring you define DataType:=xlDelimited, and set your preferred delimiter (such as Comma:=True or Space:=True).
Close the editor, go back to Excel, click 'Macros' in the Developer tab, select your new macro, and click 'Options' to assign it a quick keyboard shortcut.
Try WPS Office for Seamless Data Processing
If navigating Excel's lack of customization for certain features is slowing down your workflow, consider switching to WPS Office. It provides a lightweight, free alternative with intuitive data handling and full format compatibility.
- 1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing Excel data files seamlessly.
- 3. Process Your Data: Navigate to the Data tab and use the straightforward Text to Columns tool to split your strings accurately and efficiently.
Frequently Asked Questions
Does Excel remember my Text to Columns settings during the same session?
Yes, Excel will usually remember the specific delimiter (like a comma or space) you chose during your active session, but the initial wizard menu does not save 'Delimited' as a permanent default when restarting Excel.
Can I change the default delimiter via the Windows Registry?
No, there is no known registry key or standard Excel advanced option that allows you to force 'Delimited' as the permanent global default over 'Fixed width'.
Will applying a VBA macro affect my ability to undo the action?
Yes, one limitation of using VBA macros to split text to columns is that running a macro clears the Excel undo stack. You will not be able to press Ctrl+Z to undo the split.
