Ignoring CR when importing txt file

**Bytor47** · 09-03-2010, 11:19 AM

I am trying to figure out how to import a tab delimited .txt file that will have quoted fields that will have CRs within them. Whenever I try to import, Excel breaks up the lines when it encounters a CR in the middle of a quoted field. I need the line to stay in tact. Any help is appreciated.

If I open the .txt file in notepad and select all and then copy it into Excel, I get what I want. The problem is when I open the file in Excel and try to use the delimiters and text qualifiers. That's when Excel splits quoted fields into different lines when it encounters CRs.

I have attached a sample input file and what the desired results should be. Notice that some of the cells have multiple lines in them.

Here is the code I was using to start with:

    Workbooks.OpenText Filename:=InputFile, Origin:= _
        xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1)), TrailingMinusNumbers:=True
        
    Rows("1:" & Cells(Rows.Count, 1).End(xlUp).Row).Select
         With Selection.Font
        .Name = "Arial"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Selection.Copy
   
    ThisWorkbook.Activate
   
    Range("A7").Select
    ActiveSheet.Paste

Thanks in advance

**Leith Ross** · 09-03-2010, 01:40 PM

Hello Bytor47,

After the text has been imported, you can run this macro to remove the carriage return characters.

Sub RemoveCR()

  Dim Cell As Range
  Dim Rng As Range
  Dim RngEnd As Range
  Dim Wks As Worksheet
  
    Set Wks = Worksheets("Sheet1")
    
    Set Rng = Wks.Range("A1")
    Set RngEnd = Wks.Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False)
    
    Set Rng = Wks.Range(Rng, RngEnd)
    
      For Each Cell In Rng
        Cell = Replace(Cell.Text, vbCr, "")
      Next Cell
      
End Sub

**Bytor47** · 09-03-2010, 02:22 PM

Thanks for the reply but I can't do it that way. I am actually trying to add to an existing macro that will work with the data once it is imported.

I need the carriage returns where they are. I just don't want the import to split the data into separate lines. I want the cells to retain the soft carriage returns in them.

The data in the input file contains "" fields that may contain CRs that I want to keep. Excel keeps splitting those fields into different lines on me but I don't want that.

Thanks again

**Leith Ross** · 09-03-2010, 02:50 PM

Hello Bytor47,

Excel will make a new line in a cell when a carriage return is present in the text. I don't see any new lines (columns or rows) created by importing the text. So, I don't understand what you mean by this statement...

I need the carriage returns where they are. I just don't want the import to split the data into separate lines. I want the cells to retain the soft carriage returns in them.

**Bytor47** · 09-14-2010, 03:15 PM

What I mean is that if you look in the input file on the 4th line you will see the date and time contains a special character between them. Upon further investigation this is a LF character (Hex 0A). During my import, the macro I created ignores the fact that the LF is between quotes and splits that field over multiple lines. I don't want that to happen.

I need all of the text, including special characters, within the quotes to remain in 1 cell.

If I open the input file in notepad and select all the text and copy it into a blank worksheet, it pastes the way I want. The cell containing the Date and Time will actually contain 5 lines in it.

Unfortunately I can't seem to get my macro to import the same way.

I hope this helps.

Thanks