'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Why Excel Selects Fixed Width in Text to Columns & How to Fix It
Question details
The user needs to understand why the Convert Text to Columns Wizard in Excel defaults to the Fixed Width option instead of Delimited, and how to change this setting to properly separate data.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Splitting text data into individual columns using the Convert Text to Columns Wizard.
- Observed behavior
- Excel automatically selects the Fixed Width radio button by default based on its initial data analysis, which may not match the actual data structure or previous settings used by the user.
Before splitting your data, ensure you have inserted enough empty columns to the right of your selected data range so that the newly separated text does not overwrite your existing spreadsheet information.
Manually Select the Delimited Option in the Wizard
Override Excel's automatic formatting guess by manually choosing the Delimited option to split data separated by specific characters like commas or tabs.
When you launch the Text to Columns wizard, Excel analyzes the first few rows of your selected data to guess the best separation method. If the text appears evenly spaced, it defaults to Fixed Width. You can easily override this behavior to use your preferred delimiters.
Highlight the column containing the data you want to split. Navigate to the 'Data' tab on the top ribbon and click the 'Text to Columns' button.
In step 1 of the Convert Text to Columns Wizard, ignore the default 'Fixed Width' selection and click the radio button next to 'Delimited'.
Click 'Next' to proceed to step 2. Check the boxes for the specific separators your data uses, such as Comma, Tab, Space, or enter a Custom character in the 'Other' box.
Review the data preview at the bottom of the window to ensure the columns are splitting correctly. Click 'Next' to adjust column data formats if necessary, then click 'Finish' to apply the changes.
Split Text into Columns Easily with WPS Office
WPS Spreadsheet offers a highly intuitive Text to Columns wizard that seamlessly handles both delimited and fixed-width data, giving you complete control over your data parsing without unexpected defaults.
- 1. Select the Target Data: Open your spreadsheet in WPS Office and highlight the column containing the text you need to separate.
- 2. Access Text to Columns: Go to the 'Data' tab in the top menu and select 'Text to Columns' from the toolbar.
- 3. Choose the Split Method: In the pop-up wizard, select 'Delimited' or 'Fixed width' depending on your data structure, and click Next to define your separators.
- 4. Finish and Format: Set the destination cell and column formats, then click 'Finish' to perfectly split your data into distinct columns.
Frequently Asked Questions
Can I force Excel to always default to Delimited instead of Fixed Width?
No, there is no built-in setting or registry tweak to permanently change the default option to Delimited. Excel will always analyze the selected text dynamically and guess the most appropriate starting format for the wizard.
What is the primary difference between Delimited and Fixed Width?
Delimited is used when your text is separated by specific characters like commas, tabs, or semicolons. Fixed Width is used when your data is aligned in columns with spaces between each field, and the split happens at a specific character count rather than at a symbol.
Why did my text not split correctly after manually choosing Delimited?
This usually happens if the wrong delimiter is selected in Step 2 of the wizard, or if the text contains inconsistent separators. Ensure you have checked the correct box (e.g., Comma or Space) that exactly matches the separator used in your raw data.
How do I split text using a custom character like a hyphen?
Choose 'Delimited' in the first step of the Text to Columns wizard, click Next, check the 'Other' box in the Delimiters section, and type your custom character (such as a hyphen or slash) in the adjacent text box.
