How to Fix Excel TXT Import Merging Data and Missing Macros
Question details
The user is experiencing issues where importing a TXT file into Excel results in all data merged into a single column due to missing delimiter options. Additionally, macros are missing from the workbook.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing text files into a spreadsheet and working with existing VBA macros.
- Observed behavior
- Imported TXT data is squeezed into one column without delimiter detection, and previously created macros are no longer available or executable in the workbook.
Before proceeding, ensure you have the original TXT file accessible on your local drive and verify the file extension of your current Excel workbook.
Use Power Query to Import and Split TXT Data
Utilizing Excel's Power Query feature allows you to properly detect delimiters and split text data into separate columns during the import process.
Modern versions of Excel default to Power Query for data imports, which sometimes skips the legacy Text Import Wizard. By leveraging Power Query manually, you can explicitly set how your text file is separated.
Open your Excel workbook and navigate to the Data tab on the main ribbon.
Click on 'Get Data' > 'From File' > 'From Text/CSV'. Browse your computer, select the problematic TXT file, and click Import.
A preview window will appear. In the Delimiter dropdown menu at the top left, select the correct character separating your data (e.g., Comma, Tab, Semicolon).
Once the preview shows the data properly split into multiple columns, click 'Load' to insert the formatted data into your worksheet.

Verify File Format to Restore Missing Macros
Macros disappear if a workbook is accidentally saved as a standard Excel file (.xlsx) instead of a Macro-Enabled Workbook (.xlsm).
Easily Import TXT Data and Run Macros with WPS Spreadsheet
WPS Spreadsheet offers an intuitive legacy Text Import Wizard by default that automatically detects delimiters to prevent merged columns. It also provides seamless support for Excel macros, ensuring your automated workflows remain intact without formatting headaches.
- 1. Open WPS Spreadsheet: Launch WPS Spreadsheet and click the 'Data' tab on the top menu ribbon.
- 2. Start the Data Import: Click 'Import Data' and select 'Import Data' from the dropdown to locate and select your TXT file.
- 3. Set Data Delimiters: In the Text Import Wizard popup, choose 'Delimited', click Next, and check the correct delimiter box (e.g., Tab, Comma, Space) to separate your columns.
- 4. Complete the Import: Click 'Finish' and choose where to place the data. It will instantly populate your spreadsheet in properly separated columns.

Frequently Asked Questions
Why does Excel put all imported text into one single column?
This happens when the import tool fails to recognize the delimiter (such as a comma, tab, or space) separating your data. Manually specifying the delimiter using Power Query or the Text Import Wizard fixes this issue.
Can I retrieve macros saved in a standard .xlsx file?
No, saving a macro-enabled workbook as a standard .xlsx file strips and permanently deletes the macros from that specific file. You will need to restore a previous backup or locate the original .xlsm file.
Is there an alternative to Power Query for importing text in older Excel versions?
Yes, in older versions of Excel, you can use the legacy Text Import Wizard. Go to 'Data' > 'From Text', select your file, and follow the three-step prompts to manually set your data delimiters and column formats.
Does WPS Office support Excel macros?
Yes, WPS Spreadsheet fully supports VBA macros. You can open, edit, and run macro-enabled workbooks (.xlsm) smoothly within the WPS Office environment without losing functionality.




