How to Copy Word Data to Excel Without Shifting Empty Columns
Question details
The user needs to paste text data from a Word document into Excel without having empty fields skip and shift the remaining row values to the left.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Transferring text-based lists or pseudo-tables with inconsistent delimiters from Word into an Excel spreadsheet.
- Observed behavior
- When pasting the copied text into Excel, empty fields are ignored, which causes the subsequent data in the row to shift left, ruining the column alignment.
Before importing, save your Microsoft Word document as a Plain Text (.txt) file to preserve the raw delimiter structure and prevent automatic formatting changes.
Use Power Query to Import Text Data
Saving the Word document as a text file and importing it via Power Query gives you full control over delimiters and prevents empty fields from shifting.
Power Query is the most robust tool for handling inconsistent data structures. By importing the raw text file, you bypass Excel's default clipboard behavior which tends to ignore consecutive delimiters.
Open your Word document, go to File > Save As, and choose 'Plain Text (*.txt)' as the file format.
In Excel, navigate to the Data tab on the ribbon, click on 'Get Data' > 'From File' > 'From Text/CSV'.
Browse your computer for the text file you just saved, select it, and click 'Import'.
In the preview window, select the correct delimiter (such as Tab or Comma). Ensure that the preview shows empty fields as empty columns, then click 'Load' to bring the data into your spreadsheet.
Use the Text to Columns Wizard
If you prefer not to use Power Query, you can paste the raw text directly into Excel and use the Text to Columns tool to parse it manually.
Easily Import Word Data to WPS Spreadsheet
WPS Spreadsheet features a highly intuitive Import Data wizard that perfectly handles raw text files. You can precisely control delimiters to ensure empty columns never shift, all within a free and lightweight office suite.
- 1. Save Word data as Text: In WPS Writer or Word, save your document containing the target data as a Plain Text (.txt) file.
- 2. Open Import Data tool: Launch WPS Spreadsheet, navigate to the Data tab, and click the 'Import Data' button.
- 3. Select the text file: Choose the .txt file you saved and follow the prompts in the Text Import Wizard.
- 4. Disable consecutive delimiters: Make sure 'Treat consecutive delimiters as one' is unchecked so that empty fields stay as blank columns, then click Finish.
Frequently Asked Questions
Why does Excel shift my data to the left when I paste from Word?
This usually happens because the copied text relies on consecutive delimiters (like multiple tabs or spaces) to create visual gaps. Standard pasting often ignores consecutive delimiters, thereby skipping the empty field and pulling the next value to the left.
What does 'Treat consecutive delimiters as one' do?
If this option is checked during data import or inside the Text to Columns wizard, multiple tabs or spaces in a row are treated as a single separator. This eliminates empty columns and shifts your data left. Unchecking it preserves the empty columns.
Can I prevent shifting by formatting the data in Word first?
Yes. If you highlight your text in Word and convert it to a table (Insert > Table > Convert Text to Table), you can copy and paste that table directly into Excel. The table structure will perfectly retain empty cells and prevent shifting.
