Remove ‘ALT+ENTER’ed Line Breaks Without VBA (XL2000-XL2010) (ALT+Enter 삭제 /바꾸기)

Remove line breaks in Excel

You already know that you can enter a forced line break within a cell by pressingALT+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 type0010;. 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 clickFind 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),” “)  

 

 

ref = http://theexceladdict.com/blog/?p=128 

 

 

DB 등을 excel로 export 한 후에 보면,

음표 문자나 공백 문자가 포함된 경우가 있다.

 

이럴 경우엔,

 

1. Alt + F11 을 눌러 VBE 실행한다.

2. Ctrl + G 를 눌러 직접실행 창을 띄운다.

3. 다음을 입력하고 엔터를 누른다

 

Cells.Replace ChrW(13), ""     -> 음표 문자 제거

Cells.Replace ChrW(10), ""     -> 개행 문자 제거

Cells.Replace ChrW(160), ""    -> 유령 문자 제거

 

Cells.Replace ChrW(9), ""    -> 가로탭 문자 제거

Cells.Replace ChrW(13), ""    -> 캐리지리턴 문자 제거