How to delete carriage return in every cell in excel?

Question

I use this code for deleting carriage return from every cell in excel:

 Microsoft.Office.Interop.Excel.Range cells = reportSheet.Cells;
 cells.Replace("\n", "",
               Microsoft.Office.Interop.Excel.XlLookAt.xlWhole,
               Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, false,
               false, true, false);

I get a message:

"Microsoft Office Excel cannot find any data to replace. Check if your search formatting and criteria are defined correctly. If you are sure that matching data exists in this workbook, it may be on a protected sheet. Excel cannot replace data on a protected worksheet."

Anybody know how to change carriage return in excel cell?

Answer 1

If doing data entry in Excel and you press the Alt+Enter key, it will insert a linefeed character. However, if the input was written to the cell as a Windows line break (CR+LF), it will be displayed in Excel exactly the same way.

The safest way to ensure all line breaks are replaced is to cycle through all possibilities and replace each:

Microsoft.Office.Interop.Excel.Range cells = reportSheet.Cells;
// Cycle through each newline code and replace.
foreach (var newline in new string[] { "\r\n", "\r", "\n" })
{
    cells.Replace(newline, "",
                  Microsoft.Office.Interop.Excel.XlLookAt.xlPart, // Use xlPart instead of xlWhole.
                  Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, false,
                  false, true, false);
}