You should have space characters at the start of fields ONLY where the space characters are part of the data. Excel will not strip off leading spaces. You will get unwanted spaces in your headings and data fields. Worse, the " that should be "protecting" that line-break in the third column will be ignored because it is not at the start of the field.

If you have non-ASCII characters (encoded in UTF-8) in the file, you should have a UTF-8 BOM (3 bytes, hex EF BB BF) at the start of the file. Otherwise Excel will interpret the data according to your locale's default encoding (e.g. cp1252) instead of utf-8, and your non-ASCII characters will be trashed.

Following comments apply to Excel 2003, 2007 and 2013; not tested on Excel 2000

If you open the file by double-clicking on its name in Windows Explorer, everything works OK.

If you open it from within Excel, the results vary:

1. You have only ASCII characters in the file (and no BOM): works.
2. You have non-ASCII characters (encoded in UTF-8) in the file, with a UTF-8 BOM at the start: it recognises that your data is encoded in UTF-8 but it ignores the csv extension and drops you into the Text Import not-a-Wizard, unfortunately with the result that you get the line-break problem.

Options include:

1. Train the users not to open the files from within Excel :-(
2. Consider writing an XLS file directly ... there are packages/libraries available for doing that in Python/Perl/PHP/.NET/etc

• Open a new blank workbook.
• Click the Sheet1 name tab and choose View Code.
• When the VBE opens, paste the following into the code sheet titled Sheet1 (Code).

  Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Parent.UsedRange.Cells.Count = 1 And _
       Target.Cells.Count = 1 And _
       Target.Address(0, 0) = "A1" Then
        On Error GoTo safe_exit
        Application.EnableEvents = False
        Dim a As Long, b As Long, arr1 As Variant, arr2 As Variant, hdr As Variant, tmp As Variant
        arr1 = Split(Replace(Target.Value2, ",Name:", vbLf & "Name:"), vbLf)
        For a = LBound(arr1) To UBound(arr1)
            arr2 = Split(arr1(a), Chr(44))
            For b = LBound(arr2) To UBound(arr2)
                tmp = Split(arr2(b), Chr(58))
                hdr = Application.Match(tmp(0), Rows(1), 0)
                If IsError(hdr) Then
                    Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1) = tmp(0)
                    hdr = Application.Match(tmp(0), Rows(1), 0)
                End If
                Cells(2 + a, hdr) = tmp(1)
            Next b
        Next a
        Columns(1).EntireColumn.Delete
    End If

safe_exit:
    Application.EnableEvents = True

End Sub

   - Tap Ctrl+Q to return to your new, blank worksheet. - Open the TXT file in notepad or some other text editor and copy the whole thing. - Go back to your new, blank Sheet1 and paste the text into A1.

I struggled with this as well but heres the solution. If you add " before and at the end of the csv string you are trying to display, it will consolidate them into 1 cell while honoring new line.

csvString += "\""+"Date Generated: \n" ; 
csvString += "Doctor: " + "\n"+"\"" + "\n"; 

First I need to be honest - I have close links with the author of this software, having had quite some involvement in its development (ideas, testing etc.). I have been busted for over promotion already, but in my defense I used to constantly fight with Excel and now I use Ron's Editor daily.

As I said I use something called Ron's Editor a lot and I know it lets you change the line break format for cells and rows. However it lets you change them to other line break formats and from your question it is not clear if that is what you need.

Once you have the file as you want it in Ron's Editor it will correctly export the CSV file to Excel, without mucking up the formatting etc.

I hope that helps.