Removing line breaks in Excel

CHAR(13) and CHAR(10) are used for line breaks in Excel. You can take line breaks out of a cell by using the SUBSTITUTE formula:

=SUBSTITUTE(SUBSTITUTE(A1,CHAR(10)," "),CHAR(13)," ")

. . . .in this nested version of SUBSTITUTE formula we first enter the cell to be searched, the line break code and then the character to be swapped in for the line break.

Using CHAR(10) and CHAR(13) will not always take out all of the line breaks. If this approach fails, use the CLEAN formula. The CLEAN formula takes out characters that cannot be printed. It will remove other line breaks that the CHAR(10) and CHAR(13) don't catch.