엑셀 Excel SUBSTITUE 공백을 제거하거나 특정 문자 삭제하기

이름 같은 경우 성과 이름을 구분하기 위해 공백을 넣어서 띄우는 경우가 많습니다. 또는 인터넷에서 자료를 복사하는 경우 불필요한 데이터가 들어가서 정리할 필요가 종종 발생합니다. 이 때 사용하는 엑셀 함수가 SUBSTITUTE 나 TRIM 입니다. SUBSTITUTE 은 텍스트 문자열에서 특정 문자를 바꾸고 싶을 때 사용하는 함수로 일치하는 모든 문자를 교체합니다. 만약 특정 위치의 텍스트를 교체하고자 한다면 REPLACE 함수를 사용해야 합니다. REPLACE 사용법은 아래 포스팅을 참고하시기 바랍니다.

 

 

[참고] l  엑셀 Excel 함수 REPLACE 를 사용해서 특정 위치 텍스트 교체하기

 

▼ 샘플 데이터는 "-" 가 들어가 있는 전화번호입니다. SUBSTITUTE 함수를 사용해서 "-" 를 삭제하고 숫자만 남겨보겠습니다.

 

 

▼ SUBSTITUTE 구문에 대한 설명은 아래와 같습니다. 첫 번째 인수에 작업할 텍스트를 입력합니다. 두 번째에는 첫 번째에 입력한 텍스트에서 찾아야 하는 문자를 입력합니다. 세 번째는 찾은 문자 대신에 들어갈 값을 입력합니다. 값 없이 "" 입력하면 빈 값이 들어갑니다. 마지막 인수인 instance_num 에는 전체 텍스트에서 Old_text 값을 검색했을 때 몇 번째 값부터 바꿀지 지정합니다.

 

SUBSTITUTE(text, old_text, new_text, [instance_num]) l  text : 필수 요소. 문자를 대체할 텍스트가 포함된 셀의 참조 또는 텍스트 l  old_text : 필수 요소. 바꿀 텍스트 l  new_text : 필수 요소. old_text를 대신할 텍스트 l  instance_num 선택 요소. text에서 몇 번째에 있는 old_text를 new_text로 바꿀 것인지를 지정하는 수입니다.

 

 

▼ 공백을 제거하기 위해 SUBSTITUTE 함수를 적용한 결과입니다. 전화번호에는 "-" 가 모두 제거되고 숫자만 남았습니다. 

 

 

◎ SUBSTITUTE 이용해서 공백 제거하기

 

▼ 다음은 이름 사이에 있는 공백을 제거해 보겠습니다. Old_text 에는 " " 쌍따움표 안에 공백으로 띄우고 새로 교체할 텍스트인 New_text 에는 공백을 없앤 쌍따움표를 "" 입력합니다. 

 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 Excel 앞 뒤 공백 제거하는 방법, 바꾸기와 TRIM() 함수 이용 ▶ 엑셀 Excel 빈셀 찾아서 정리하기, 영역에 빈 셀 행, 열 삭제 하기 ▶ 엑셀 Excel 빈 셀 판단하는 ISBLANK 함수 사용하기 ▶ 엑셀 Excel 데이터 중 빈 셀에 포함한 행 전체 삭제하기 ▶ 엑셀 Excel 이동 옵션을 이용해서 빈 셀에 값 채우기

 

 

▼ 공백을 제거하기 위해 SUBSTITUTE 함수를 적용한 결과입니다. 공백이 하나 이상이라도 모두 제거가 되었습니다. 

 

 

◎ 두 번째 이상부터 특정 문자 제거하기

 

▼ 이번에는 마지막 인수인 Instance_num 을 사용해서 값을 구해 보겠습니다. 지금까지 4가지 인수 중에 마지막 Instance_num 은 사용하지 않았습니다. 이것은 문자를 교체하는 차례를 지정하는 옵션으로 숫자 "2" 를 입력하면 교체할 문자의 2번째부터 바꾸라는 의미를 가지고 있습니다. 예를 들어 보겠습니다. 전화번호에서 2번째에 나오는 하이픈을 제거하기 위해 Instance_num  값으로 2를 입력할 것입니다. 

 

▼ 결과는 다음과 같습니다. 전화번호에서 2번째 "-" 하이픈부터 제거했습니다.  

 

 

◎ 아스키값(ASCII) 값 이용해서 변경하기

 

▼ SUBSTITUTE 에서 인수에 텍스트임을 알리기 위해 "" 쌍따움표로 감쌌습니다. 인수로 들어가는 값을 텍스트로 인식시키는 또 다른 방법은 아스키값을 사용하는 것입니다. "-" 의 아스키값 10진수는 45 입니다. 다른 것은 필요 없고 10진수 값만 알면 CHAR() 함수와 같이 사용해서 동일한 문자로 인식시킬 수 있습니다. 

 

▼ 수식은 다음과 같습니다. 두 번째 인수에 "-" 대신 CHAR(45) 를 입력해서 수식을 완성했습니다. 

 

▼ 결과는 다음과 같습니다. "-" 입력해서 완성한 수식의 결과값과 같다는 것을 알 수 있습니다. 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 Excel 앞 뒤 공백 제거하는 방법, 바꾸기와 TRIM() 함수 이용 ▶ 엑셀 Excel 빈셀 찾아서 정리하기, 영역에 빈 셀 행, 열 삭제 하기 ▶ 엑셀 Excel 빈 셀 판단하는 ISBLANK 함수 사용하기 ▶ 엑셀 Excel 데이터 중 빈 셀에 포함한 행 전체 삭제하기 ▶ 엑셀 Excel 이동 옵션을 이용해서 빈 셀에 값 채우기