Removing line breaks from cells (the ALT ENTER’s)

Removing line breaks from cells

Many Excel users know how to create line breaks in Excel (see image) so that the ‘wrap text’ within a cell, wraps at the words you want it to wrap at. It is created by using ALT + ENTER (you can see how in our free writing paragraphs lesson). But what about removing line breaks from cells (the ALT ENTER’s)? Some software exports files to Excel with these ALT ENTER line breaks and you may need to (quickly) remove them.

YouTube Removing line breaks from cells

Watch, like, subscribe

Quick Manual Way to remove all ALT ENTER line breaks

These line breaks are actually characters in the cell (character 10- see more about this below), even though you can’t see or click on them. So just like if I wanted to get rid of all the comma’s I could use the Find Replace tool, I can do the same thing with line breaks BUT there is a trick.

In order to fill the find cell with this ‘character’ you need to click CTRL + J. So as shown below, you click in the ‘Find what’ section and hold CTRL down and push the J key. Nothing will seem to happen but Excel now knows what to look for. You leave the ‘Replace with’ blank.

Removing line breaks from cells

As shown below, Excel will find and remove the ALT ENTER line breaks and the cell will be seen as a single line.

Building a formula to remove the ALT ENTER line breaks

If you need to build a formula to remove these line breaks all you need to know is that this ‘character’ is character 10 in Excel. You can create this character in an Excel cell with the formula =CHAR(10).

So to remove it we can use the SUBSTITUTE formula and replace CHAR(10) with nothing ( shown as “”).

Removing line breaks from cells

The end result will be that the cell will revert back to a cell with no line breaks.

Removing line breaks from cells

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.

Line break as the delimiter in Text to Columns in Excel