Import txt file with line breaks into Excel

10
votes

while working on an export to Excel I discovered the following problem.

If you create a table where one cell has a line break and you save the document as a txt file it will look like this:

"firstLine<LF>secondLine"<TAB>"secondColoumn"

When I open this file in Excel the line break is gone and the first row has only one cell with the value firstLine

Do you know if it is somehow possible to keep the line breaks?

EDIT: Applies to Excel2010. Don't know if other versions behave different.

EDIT2: Steps to reproduce:

  1. Open blank excel sheet
  2. Enter text (first column with line break, second colum not important)
  3. Save as Unicode Text (txt) // all other txt don't work as well
  4. Close Excel file
  5. File->Open
  6. No changes in the upcoming dialog.

The excel file has now 2 rows which is wrong.

2
excelvba
I'm trying to reproduce your steps. Where are you creating the table? In Excel? What type are you saving it as - Text (tab delim), MS-DOS Text, Unicode text, something else? How are you "opening" the file? Importing it, dragging and dropping into Excel, or going to File > Open? – JimmyPena
And you are pressing Alt+Enter to get the line break, correct? – JimmyPena
This is correct. The line break get saved as a line-feed as shown above. – T3rm1

2 Answers

5
votes

I was finally able to solve the problem! yay :D

CSV:

The german Excel needs a semicolon as a separator. Comma doesn't work.

Note: This is only true when the file is encoded as UTF-8 with BOM at the beginning of the file. If it's ASCII encoded comma does work as a delimiter.

TXT:

The encoding has to be UTF-16LE. Also it needs to be tab delimited.

Important: The files will still be displayed incorrect if you open them with the "File->Open" dialog and "import" them. Draging them into Excel or opening with double click works.

1
votes

It isn't a problem - in the sense of expected behaviour - this is inherent when you save text as Unicode or as Text (tab delimited)

If you save the file as unicode and then either

  1. Open it in Notepad
  2. Import it in Excel

you will see that the cells with linebreaks are surrounded by ""

The example below shows two linebreaks

  • A1 has an entry separated using Alt+Enter
  • B1 has an enry using the formula CHAR(10) initial

The picture also shows what notepad sees on a saved Unicode version

Suggested Workaround 1- Manual Method

  1. In Excel, choose Edit>Replace

  2. Click in the Find What box

  3. Hold the Alt key, and (on the number keypad), type 0010step 3

  4. Replace with a double pipe delimiter

    Step 4

  5. Save as Unicode

  6. Then reverse the process when needed to reinsert the linebreaks

This can be done easily in VBA

Suggested Workaround 2 - VBA alternative

Const strDelim = "||"

Sub LBtoPIPE()
ActiveSheet.UsedRange.Replace Chr(10), strDelim, xlPart
ActiveSheet.UsedRange.Replace "CHAR(10)", strDelim, xlPart
End Sub

Sub PIPEtoLB()
ActiveSheet.UsedRange.Replace strDelim, Chr(10), xlPart
ActiveSheet.UsedRange.Replace strDelim, "CHAR(10)", xlPart
End Sub