엑셀 공백을 없애거나 특정 문자를 삭제하고 싶을 때 사용하는 함수

 

 환경:  Microsoft Excel 2010

 

이름 같은 경우 성과 이름을 구분한다고 공백을 넣어서 띄우는 경우가 있습니다. 혹은 인터넷에서 자료를 복사하는 경우 들어가 있을 수 있습니다. 이럴 때 사용하는 함수가 SUBSTITUTE TRIM 입니다. 이번에는 SUBSTITUTE 사용해서 제거하는 방법에 대해 알아 봅니다.

 

먼저 수식 입력란에 함수마법사 아이콘을 클릭합니다. 그리고 함수 검색에 SUBSTITUTE 를 입력합니다.

 

엑셀 공백을 없애거나 특정 문자를 삭제하고 싶을 때 사용하는 함수

 

실행하시면 총 4개의 인수목록을 볼수 있습니다. 인수중 Old_text New_text 만 아시면 됩니다. Instance_num 은 전체 텍스트에서 Old_text 값을 검색했을 때 몇 번째 값을 바꾸라고 지정하는 곳입니다.

 

l  Text : 작업할 텍스트

l  Old_text : 변경할 값

l  New_text : 바꿀 값

l  Instance_num : 몇번째 Old_text 의 값을 변경할 것인지 숫자

 

Text 에 이름이 들어가 있는 셀을 지정하고 Old_text 에 공백을 넣습니다. 텍스트라는 것을 표시하기 위해 항상 쌍따움표(”)로 감싸야 합니다. 새로 바꿀 데이터인 New_text 는 공백이 없는 값으로 하시면 되겠죠.

엑셀 공백을 없애거나 특정 문자를 삭제하고 싶을 때 사용하는 함수

 

 

아래 그림은 공백을 제거하기 위해 SUBSTITUTE 함수를 적용한 결과 입니다. 공백이 하나 이상이라도 모두 제거 되었죠.

엑셀 공백을 없애거나 특정 문자를 삭제하고 싶을 때 사용하는 함수

 

다음은 전화번호에 있는 하이픈(-) 을 제거해 보겠습니다. Old_text 에 하이픈을 넣고 New_text 에는 빈값을 쌍따움표로 감싸서 넣으면 되겠죠.

엑셀 공백을 없애거나 특정 문자를 삭제하고 싶을 때 사용하는 함수

 

전화번호에 하이픈을 없애기 위해 SUBSTITUTE 함수를 적용한 결과 입니다.

엑셀 공백을 없애거나 특정 문자를 삭제하고 싶을 때 사용하는 함수

 

다음은 하이픈을 제거하는데 국번에 해당하는 앞자리만 남겨두고 뒤에 있는 두번째 하이픈을 제거하는 것입니다. 이때 4번째 인수인 Instance_num을 사용하시면 되겠죠. 전화번호에서 2번째에 있는 하이픈만 삭제하면 되므로 2를 입력합니다.

엑셀 공백을 없애거나 특정 문자를 삭제하고 싶을 때 사용하는 함수

  

아래는 전화번호에서 2번째 하이픈을 제거한 결과 입니다.

엑셀 공백을 없애거나 특정 문자를 삭제하고 싶을 때 사용하는 함수

 

 

함수의 인수로 입력되는 값은 텍스트이므로 쌍따움표(“) 로 감싸게 됩니다. 그럼 문자열에 쌍따움표(“) 있을때 어떻게 제거할까요? 데이터를 가져와서 붙여넣기 할 때 종종 있는 일입니다. 쌍따움표의 아스키값(ASCII) 34 입니다.

엑셀 공백을 없애거나 특정 문자를 삭제하고 싶을 때 사용하는 함수

 

함수 인수에서 텍스트를 감쌀 때 쓰는 쌍따움표와 중복해서 Old_text 에 넣지 못하기 때문에 아스키값을 넣어서 인식하도록 했습니다. 아래 그림에서 보시면 제대로 제거된 것을 알 수 있습니다. 

엑셀 공백을 없애거나 특정 문자를 삭제하고 싶을 때 사용하는 함수

 

Posted by 녹두장군