How to Add New Rows When Importing Delimited Text Files into Excel
Question details
The user needs to know how to structure a delimited text file so that imported data properly generates new rows or new lines within a single cell in Excel.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing data from a delimited text file (like CSV or TXT) into a spreadsheet.
- Observed behavior
- A standard line break creates a new row, while a line-feed character inside quoted text creates a new line within the same cell.
Ensure you have a basic text editor like Notepad or an advanced editor like Notepad++ installed to view and modify the raw text file structure before importing it.
Create Line Breaks Within a Single Cell During Import
Use this method when you want multiple lines of text to stay within the same spreadsheet cell instead of spilling over into a new row.
In delimited files, standard line breaks signify the end of a row. To bypass this and keep text in one cell, you must use a specific line-feed character and enclose the cell contents in text qualifiers.
Open your delimited text file using a text editor.
Locate the text you want to separate into multiple lines. Insert a line-feed character (such as LF, vbLf, or ASCII character 10) exactly where the break should occur.
Enclose the entire content of that specific cell in double quotation marks (e.g., "First Line[LF]Second Line"). This tells the spreadsheet program to treat the line feed as part of the cell's text.
Save the file and import it into Excel. Once the data is loaded, highlight the imported cells and click 'Wrap Text' on the Home tab to make the multi-line content visible.
Import Delimited Text Files Seamlessly with WPS Spreadsheet
WPS Spreadsheet provides a robust text import wizard that accurately interprets line breaks, text qualifiers, and delimiters, making data formatting hassle-free.
- 1. Launch WPS Spreadsheet: Open a new or existing workbook in WPS Spreadsheet.
- 2. Start the import process: Go to the 'Data' tab on the top ribbon and click on 'Import Data' to select your delimited text file (.txt or .csv).
- 3. Configure the Text Import Wizard: Choose 'Delimited' and click Next. Select your specific delimiters (e.g., Comma, Tab) and make sure the Text Qualifier is set to double quotes (").
- 4. Finish and format: Click 'Finish' to complete the import. Select your cells and apply 'Wrap Text' from the Home tab to properly display line breaks inside the cells.
Frequently Asked Questions
Why does my imported text split into multiple rows instead of staying in one cell?
This happens because standard line breaks (Carriage Return + Line Feed) in a text file tell the spreadsheet to start a completely new row. To keep text in a single cell, you must use only a Line Feed (LF) and enclose the entire cell's text in double quotation marks.
What is a text qualifier during data import?
A text qualifier, usually a double quotation mark ("), is used to enclose a string of text. Any delimiters or line feeds inside these quotes are treated as regular text content rather than a command to start a new column or row.
How do I type a line feed character in a text file?
In advanced text editors like Notepad++, you can use the extended search and replace feature to insert a Line Feed (\n). If you are using a programming script (like Python or VBA) to generate the file, you can output ASCII character 10 or vbLf directly into the string.
