Please Enable JavaScript!
Gon[ Enable JavaScript ]

반응형

엑셀 Excel 공백을 제거하거나 특정 문자 삭제하고 싶을 때 SUBSTITUE 함수 사용

 

환경: Excel 365

 

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

 

[참고]

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

 

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

엑셀 Excel 공백을 제거하거나 특정 문자 삭제하고 싶을 때 SUBSTITUE 함수 사용

 

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로 바꿀 것인지를 지정하는 수입니다.

엑셀 Excel 공백을 제거하거나 특정 문자 삭제하고 싶을 때 SUBSTITUE 함수 사용

 

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

엑셀 Excel 공백을 제거하거나 특정 문자 삭제하고 싶을 때 SUBSTITUE 함수 사용

 

SUBSTITUTE 이용해서 공백 제거하기

 

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

엑셀 Excel 공백을 제거하거나 특정 문자 삭제하고 싶을 때 SUBSTITUE 함수 사용

 


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

엑셀 Excel 공백을 제거하거나 특정 문자 삭제하고 싶을 때 SUBSTITUE 함수 사용

 

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

 

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

엑셀 Excel 공백을 제거하거나 특정 문자 삭제하고 싶을 때 SUBSTITUE 함수 사용

 

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

엑셀 Excel 공백을 제거하거나 특정 문자 삭제하고 싶을 때 SUBSTITUE 함수 사용

 

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

 

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

엑셀 Excel 공백을 제거하거나 특정 문자 삭제하고 싶을 때 SUBSTITUE 함수 사용

 

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

엑셀 Excel 공백을 제거하거나 특정 문자 삭제하고 싶을 때 SUBSTITUE 함수 사용

 

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

엑셀 Excel 공백을 제거하거나 특정 문자 삭제하고 싶을 때 SUBSTITUE 함수 사용

반응형
Posted by 녹두장군

댓글을 달아 주세요

  1. ㅇㄴ 2015.10.16 17:15  댓글주소  수정/삭제  댓글쓰기

    감사합니다 도움됐어요~!

  2. 2015.11.23 17:25  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  3. 감사합니다 2021.05.05 18:59  댓글주소  수정/삭제  댓글쓰기

    도움 많이 되었습니다. 엑셀 잘 하시는거 너무 부럽습니다 ^^

  4. ^^ 2021.05.24 14:31  댓글주소  수정/삭제  댓글쓰기

    완전 도움 됬어요~~
    중간 설명이 생략되서 맞나? 싶었지만 역시 엑셀은 사랑이네요^^