Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to delete carriage return in every cell in excel?

Tags:

c#

excel

cell

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?

like image 843
Horman Lewis Avatar asked Jan 11 '15 16:01

Horman Lewis


1 Answers

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);
}
like image 120
Jason Faulkner Avatar answered Oct 12 '22 23:10

Jason Faulkner