Remove ‘ALT+ENTER’ed Line Breaks Without VBA (XL2000-XL2010)

Remove line breaks in Excel

You already know that you can enter a forced line break within a cell by pressing ALT+ENTER, right? If you you want to get rid of the line break you can simply click immediately before the line break and press Delete or click immediately after the line break and press Backspace. 

But what if you have a worksheet that has a lot of these line breaks and you need to get rid of them? You may think that this would require a macro. You’d be wrong! 

Here’s a non-VBA method to quickly get rid of all those line breaks in your worksheet. If you want to remove them from only part of the worksheet, select that range first. Otherwise, select any single cell. 

1) Press CTRL+H (i.e. shortcut for Find and Replace); 

2) Click in the ‘Find what‘ field, hold down the ALT key and type 0010;. It may not seem like anything happened but you actually entered an invisible line break character; 

3) Click in the ‘Replace with‘ field, press the spacebar once and click Find All. You will get a list of all cells containing a line break; 

4) At this point you can choose to Replace All

It may not be apparent but you may have caused some cells to have two blank spaces. You can easily find and get rid of them by repeating the previous 4 steps, using two blank spaces in the ‘Find What‘ field and one blank space in the ‘Replace with‘ field. 

FYI, you can also use a formula to remove these line breaks. 

=SUBSTITUTE(A1,CHAR(10),” “) 

 
 
 

 

Leave a Reply