엑셀 SUBSTITUTE 함수로 공백 제거 또는 특정 단어 삭제 및 교체하는 방법

대표적인 스프레드시트 인 마이크로소프트 사의 엑셀을 활용해 데이터를 관리 할 때, 특정 문자 또는 공백을 다른 문자로 일괄 교체 하거나 제거 하고 싶을 수 있습니다.

이 때 SUBSTITUTE 함수를 사용하면 손쉽게 텍스트 문자열에서 특정 문자를 다른 문자로 변경하거나 제거 할 수 있습니다.

그래서 오늘은 '엑셀'에서 SUBSTITUTE 함수로 공백을 제거하거나 특정 단어를 삭제 또는 교체하는 방법에 대해서 알아 봅니다.


튜토리얼 환경 : 윈도우 11, 마이크로소프트 365 엑셀 2022

해당 포스트는 충분한 테스트 및 검증 후 작성 되었지만 이것이 내용의 정확성이나 신뢰성에 대해 보증을 하는 것은 아니니 단순 하게 참고용으로 봐주시길 바랍니다.

SUBSTITUTE 구문

SUBSTITUTE 함수를 사용하기 전 기본적인 SUBSTITUTE 구문에 대해 정리합니다.

SUBSTITUTE_함수_인수
SUBSTITUTE 함수 인수

함수 인수 패널을 통해 기본적인 SUBSTITUTE 구문을 확인 할 수 있으며 실제 표기 되는 형식은 아래와 같습니다.

SUBSTITUTE(Text, Old_text, New_text, [Instance_num]) 
  • Text: 문자를 대체할 텍스트가 포함된 셀의 참조 또는 텍스트 이며, 필수 요소입니다.
  • Old_text: 바꿀 텍스트 이며, 필수 요소입니다.
  • New_text: Old_text를 대신할 텍스트 이며, 필수 요소입니다.
  • Instance_num: text 에서 몇 번째에 있는 Old_textNew_text 로 바꿀 것 인지를 지정하는 수이며, 선택 요소입니다.
엑셀 파일 손상 되어 열리지 않을 때 조치 방법 몇 가지

SUBSTITUTE 로 특정 문자 교체 또는 제거하기

SUBSTITUTE 를 사용 하면 다양한 방식으로 특정 문자 또는 공백을 교체 하거나 제거 할 수 있습니다.

특정 문자 교체하기

SUBSTITUTE 함수를 사용하면 아주 간단하게 특정 문자를 교체하는 기본적인 방법입니다.

문자를_다른_문자로_일괄_변경하기
문자를 다른 문자로 일괄 변경하기

기본 구문에 따라 =SUBSTITUTE(A2,"-","=") 과 같이 작성하면 A2 셀의 문자열에서 - 를 찾아 = 로 교체합니다.

Excel 엑셀 통합문서 스크롤바 숨기거나 표시하는 방법

공백 제거 또는 교체하기

기본적으로 앞서 설명한 문자를 제거하거나 교체하는 방식과 동일합니다.

다만, 엑셀에서 공백을 제거 하거나 교체 하기 위해서는 특정 문자 대신 공백이 포함 된 큰 따옴표 (" ") 와 공백이 없는 큰 따옴표 ("") 를 활용하면 됩니다.

특정 문자를 공백으로 교체하기

문자를_공백으로_변경하기
문자를 공백으로 변경하기

기본 구문에 따라 =SUBSTITUTE(A2,"-"," ") 과 같이 작성하면 A2 셀의 문자열에서 - 를 찾아 공백 으로 교체합니다.

공백 제거하기

공백을_제거하기
공백을 제거하기

기본 구문에 따라 =SUBSTITUTE(A2," ","") 과 같이 작성하면 A2 셀의 문자열에서 공백 을 찾아 값이 없는 상태로 제거 처리 됩니다.

특정 위치 이후 문자 교체하기

Instance_num 인수를 사용하면 대상 문자의 특정 순번 이후 문자를 교체하거나 제거 할 수 있습니다. 예를 들어 A 가 여러 개 포함 된 문자열에서 두 번째, 세 번째 또는 네 번째 이후에 표기 된 A 만을 대상으로 할 수 있습니다.

Instance_num_인수로_특정_위치_이후_문자_교체하기
Instance num 인수로 특정 위치 이후 문자 교체하기

기본 구문에 따라 =SUBSTITUTE(A2,"-","&",2) 와 같이 작성하면 A2 셀의 문자열에서 -& 로 교체 합니다. 다만 [Instance_num] 값이 2 로 설정 되어 있어 두 번째 부터 위치한 - 만을 대상으로 합니다. 만약 [Instance_num] 값이 3 으로 되어 있다면 세 번째 이후 값을 대상으로 합니다.

오피스 파워포인트 워드 엑셀 한글 버전 영문 버전 변경하기

아스키 코드 활용(선택)

엑셀에서 텍스트를 인수로 사용하기 위해서는 큰 따옴표 ( "" )로 감싸 주어야 합니다. 이 경우 인수에 사용되는 문자 또는 기호와 중첩이 발생 할 수 있는데 이 때 아스키 코드를 사용하면 조금 더 확실하게 원하는 값을 인수로 지정할 수 있습니다.

아스키_코드로_문자_교체_하기
아스키 코드로 문자 교체 하기

아스키 코드 중 - 는 10 진수로 45 이며, 이 값을 CHAR() 과 함께 표기하면 동일한 문자로 인식됩니다. 10 진수 아스키 코드 값은 아스키 코드표에서 확인하세요.

위 아스키 코드로 기본 구문에 따라 =SUBSTITUTE(A2,CHAR(45),"=") 와 같이 작성하면 A2 셀의 문자열에서 - 를 찾아 = 로 교체합니다.

마무리

이렇게, '엑셀'에서 SUBSTITUTE 함수로 공백을 제거하거나 특정 단어를 삭제 또는 교체하는 방법에 대해 알아 보았습니다.

SUBSTITUTE 는 자료를 가공 할 때 자주 사용되는 함수 입니다. SUBSTITUTE 구문 자체가 간단해 처음 사용하는 경우에도 어렵지 않게 활용 할 수 있습니다.

엑셀에서 문자열로 된 데이터를 관리 하는 경우 해당 문자열에 공백을 추가하거나 특정 문자를 대량으로 변경 해야 하는 경우 앞서 설명한 SUBSTITUTE 함수를 활용 해 보시길 바랍니다.

마이크로소프트 오피스365 엑셀 파워포인트 워드 2021 새로운 UI 테마 적용하기

참고

댓글로 남기기 어려운 내용은 Contact Form 에서 개별적으로 문의 할 수 있습니다. 해당 글과 연관 된 내용은 Copy를 이용해 현재 페이지의 주소를 복사 후 문의 폼에 입력시 보다 정확한 답을 얻을 수 있습니다.

Leave a Comment