Ignoring CR when importing txt file

[Bytor47]

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:

Please Login or Register  to view this content.

Thanks in advance

[Leith Ross]

Hello Bytor47,

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

Please Login or Register  to view this content.

[Bytor47]

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]

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]

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

[shg]

They're line feeds, not carriage returns. Try

Please Login or Register  to view this content.

[Bytor47]

That worked great. Thanks.

Who knew 4 little characters could make such a difference when opening a .txt file.

Thanks again.