CODEWITHSUNDEEP
excelexcel-import
live-love
live-love

Reputation: 52366

Excel Import File with line breaks inside a cell

I'm using Excel 2013. I am having problem importing a simple .txt file with a line break character /n or 0x0A in it. Excel is importing the character as a new row, so it's not inside the same cell. I want this character to appear inside the cell.

File Dump: 31 0A 32 09 33 34
1_2_34

Text Import Wizard Settings:

Delimited

Windows ANSI

Tab

Text Qualifier: none

Results:

1   
2   34

I've also tried enclosing the text in double quotes, with no success.

22 31 0A 32 22 09 22 33 34 22
"1_2"_"34"

Results:

1   
2"  34

Upvotes: 1

Views: 417

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

This little macro will pull the characters in (one at a time) and place the entire string in a single cell:

Sub Macro1()
    Dim s As String
    Open "C:\Users\James\Desktop\Book1.txt" For Input As #1 Len = 1
    Do Until EOF(1)
        s = s & Input(1, #1)
    Loop
    Range("A1").Value = s
    Close #1
End Sub

Upvotes: 1

Related Questions