Menu
Remove line breaks in Excel. Very similar to search for line breaks you can also remove or replace them.
Learn about the risks of blank rows and columns, and why locking formulas, sorting, and saving often are a good practice. Updated to include Excel 2019.
Instead of “Find”, use the “Replace” tab of the search window and replace them with a blank space (” “). Open the search window by pressing Ctrl + f on the keyboard. Type Ctrl + j into the search field and press Enter.
As the “Replace with” value enter a space character. Click on “Replace All” or just “Replace” if you want to go cell by cell. In some cases, there a space characters at the end of a line. When that happens, you will get a double space. You might want to use the replace function again, but this time replace all double spaces (type 2x space on the keyboard) by just one space. Remove line breaks in Excel with the SUBSTITUE formula. Of course, replacing or removing line breaks is also possible using formulas.
The easiest way is to use the. If your input text is located in cell B3, the formula looks like this: = SUBSTITUTE ( B3, CHAR ( 10 ), ' ' ). The first part contains the input text.
In our case it’s cell B3. The second part has the text which you want to replace. In our case it’s the line break, given by CHAR(10). In the third part you provide the replacement.
Usually it’s a space character. As sometimes the lines already end with a space, you might get double spaces. We recommend replacing double spaces by single space characters. Therefore, just use the SUBSTITUTE formula again and wrap it around the existing SUBSTITUTE formula: = SUBSTITUTE ( SUBSTITUTE ( B3, CHAR ( 10 ), ' ' ), ' ', ' ' ).
Excel 2013 automatically displays lengthy text entries over adjacent cells, assuming no other data exists in these cells. However, this behavior could extend the text outside the visible area of the worksheet and any adjacent data added to the spreadsheet could truncate the paragraph. Employing one or more options can force the paragraph to fit in the row without worrying that subsequent data will conceal part of your text.
Of these options, allowing text wrap and merging adjacent cells potentially offer the most benefit.