How to Fix Excel Get and Transform Import Problems for Text Files
Question details
The user needs to resolve missing columns and incorrect parsing when importing space-delimited or fixed-width files (such as S2P files) into Excel.

- Product
- Microsoft Excel 365
- Device & OS
- not provided
- Scenario
- Importing text files containing space-delimited or fixed-width data into an existing worksheet layout.
- Observed behavior
- Using the default Get and Transform Data feature in Microsoft 365 produces missing columns and incorrectly parses the S2P data compared to the legacy text import wizard in older Excel versions.
Ensure you have a representative sample of your S2P or text file ready, and open it in Notepad to verify whether the source data uses consistent space delimiters or strict fixed-width columns.
Configure Delimiters Using Get and Transform Data (Power Query)
Use the Power Query Editor in Microsoft 365 to manually adjust the delimiter settings and transformation steps before loading the data into your worksheet.
Microsoft 365 replaced the classic Text Import Wizard with Get and Transform Data (Power Query). While more powerful, its automatic delimiter detection can fail on complex files like S2P data, merging consecutive spaces and dropping columns. You must manually intervene in the transform steps.
In Excel, navigate to the 'Data' tab, click 'Get Data', select 'From File', and then choose 'From Text/CSV'. Select your S2P or text file and click 'Import'.
When the preview window appears showing the incorrectly parsed data, do not click 'Load'. Instead, click the 'Transform Data' button at the bottom.
In the Power Query Editor, look at the 'Applied Steps' pane on the right. Click the 'X' next to 'Changed Type' and 'Promoted Headers' if they were incorrectly applied.
Select the first column containing your combined data. Go to the 'Home' tab, click 'Split Column', and choose 'By Delimiter'. Select 'Space' as the delimiter, and ensure you uncheck 'Treat consecutive delimiters as one' if you need empty columns preserved, or use 'By Number of Characters' for fixed-width data.
Once the preview matches your desired layout with all columns intact, click the 'Close & Load' dropdown at the top left, select 'Close & Load To...', and choose 'Existing worksheet' to place the data exactly where you need it.

Import Space-Delimited and Fixed-Width Files Seamlessly in WPS Spreadsheet
If Microsoft 365's Get and Transform Data feels too complicated or routinely corrupts your S2P engineering files, WPS Spreadsheet offers a highly intuitive Text Import Wizard. It functions identically to the classic Excel import tool, giving you complete visual control over space delimiters and fixed-width column breaks without requiring Power Query knowledge.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing Spreadsheet where you want to import your text file.
- 2. Launch the Import Wizard: Navigate to the 'Data' tab on the ribbon and click on 'Import Data'. Select 'Import Data' again from the drop-down menu.
- 3. Select your file: Choose 'Select Data Source', browse your computer for the S2P or text file, and click 'Open' to trigger the Text Import Wizard.
- 4. Configure delimiters: Choose 'Delimited' (for space-separated values) or 'Fixed width'. On the next step, check the 'Space' box or visually adjust the column break lines by clicking on the preview pane to ensure no columns are missing.
- 5. Finish and place data: Click 'Finish', choose the exact cell where you want the imported data to start, and click 'OK'.

Frequently Asked Questions
Why does Microsoft 365 drop columns when importing text files?
By default, Microsoft 365 uses Power Query to import text files. Power Query tries to automatically detect delimiters and data types. If a file uses consecutive spaces or irregular fixed-width spacing (like S2P files), Power Query may merge these spaces, resulting in missing or concatenated columns unless manually configured.
How do I import fixed-width data into Microsoft Excel 365?
To import fixed-width data, go to Data > Get Data > From File > From Text/CSV. After selecting your file, click 'Transform Data'. Select your data column, click 'Split Column' under the Home tab, and choose 'By Number of Characters' or 'By Positions' to manually define the column widths.
What is an S2P file and how do I open it in a spreadsheet?
An S2P file is a Touchstone parameter file used in RF engineering to describe multi-port networks. It contains plain text data, usually space-delimited. You can open it in any spreadsheet application by importing it as a standard text file (.txt) and applying space delimiters.




