Data Import & Export

How to Align Text File Headers Correctly When Importing into Excel

Steve K Sep 29, 2026 869 views

Question details

The user needs to properly align text file headers with the correct columns when importing the data into Excel.

How to Align Text File Headers Correctly When Importing into Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Importing a text file into a spreadsheet where header columns must match the subsequent data columns.
Observed behavior: Excel assigns data to columns based on delimiter positions rather than the visual spacing seen in text editors, causing headers to misalign if relying on spaces instead of proper delimiters.

Before you start

Open your source text file in a plain text editor like Notepad++ to verify its structure. Identify which character (such as a comma, tab, or pipe) should act as your primary column separator before beginning the import process.

Solution 1Recommended

Use Custom Delimiters to Force Column Alignment

Adjust the text file by adding the correct number of delimiters (like pipe characters) to force headers into specific Excel columns during import.

Excel does not respect the visual spacing (like multiple spaces) shown in a standard text editor. Instead, it relies on strict delimiter characters to decide where a new column begins.

If you want a specific header to appear in the fifth column of your spreadsheet, you must place four delimiters immediately before it in your text file.

Open the text file in an editor

Right-click your source text file and select 'Open with', then choose a text editor like Notepad or Notepad++.

Insert required delimiters

Locate the header row that needs alignment. Insert the required number of delimiter characters (e.g., the pipe character '|') between the fields. For instance, to push a header from column two to column five, insert three additional pipe characters between the first and second header names.

Save the updated text file

Press Ctrl + S to save the changes to your text document and close the text editor.

Import into Excel

Open Excel, navigate to the 'Data' tab on the ribbon, and click 'From Text/CSV'. Select your saved text file and click 'Import'.

Specify the delimiter in the wizard

In the import preview window, locate the Delimiter dropdown menu. Select 'Custom' and type the pipe character '|' (or your chosen delimiter). Verify the alignment in the preview and click 'Load'.

Use Custom Delimiters to Force Column Alignment

Pro Tip: Ensure that the option 'Treat consecutive delimiters as one' is unchecked in the legacy Text Import Wizard if you are using multiple delimiters to intentionally create blank columns.

Seamless Data Import

Effortlessly Import and Align Text Data with WPS Spreadsheet

WPS Office Spreadsheet provides a seamless and highly compatible Text Import Wizard, allowing you to quickly separate text into columns using custom delimiters like pipes, commas, or tabs.

1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing Spreadsheet document.
2. Navigate to the Data tab: Click on the 'Data' tab located on the top ribbon menu.
3. Select Import Data: Click on 'Import Data' and choose 'Import Data' again from the dropdown list. Select the text file you wish to import.
4. Configure the Text Import Wizard: In the wizard, select 'Delimited' and proceed to the next step. Check the box for 'Other' and type your chosen delimiter, such as the pipe '|' character.
5. Preview and Finish: Check the data preview window to ensure your headers and columns are perfectly aligned, then click 'Finish' to complete the import.

Easily align headers using custom delimiters during the text import process.Full compatibility with Microsoft Excel file formats (.xlsx, .csv, .txt).Built-in data preview to ensure perfect column alignment before finalizing the import.Free, lightweight, and features a highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why do my imported text headers look aligned in Notepad but not in Excel?

Text editors often use spaces or tabs for visual alignment on your screen. However, Excel strictly relies on delimiters (like commas, tabs, or pipes) to define columns. If the correct number of delimiters isn't present in the raw text, Excel will not push the data to the corresponding column.

Can I use multiple consecutive delimiters to skip columns?

Yes. If you place consecutive delimiters (for example, '|||') in your text file, Excel will interpret them as empty columns and push your subsequent data further to the right. Just make sure the 'Treat consecutive delimiters as one' option is disabled during the import.

How do I change the delimiter character during the Excel import process?

When using the Text Import Wizard or Power Query, you can select the delimiter type from a dropdown menu. If using a custom character like a pipe ('|'), select 'Custom' or 'Other' and type the specific character into the provided input box.

Is there a way to align text to columns after importing?

Yes. If your data imports incorrectly into a single column, you can highlight that column, go to the 'Data' tab, and click 'Text to Columns'. This will re-open the wizard, allowing you to split the data across columns using your chosen delimiter.

Excel & Data Problems