엑셀 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 로 바꿀 것 인지를 지정하는 수이며, 선택 요소입니다.
오피스 Excel 엑셀 자동 번호 매기기 방법

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 함수를 활용 해 보시길 바랍니다.

오피스 엑셀 파워포인트 워드 개발 도구 활성화 와 매크로 보안 설정하는 방법

참고

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

Leave a Comment