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.
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.
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 “”).
The end result will be that the cell will revert back to a cell with no line breaks.
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.