대표적인 스프레드시트 인 마이크로소프트 사의 엑셀을 활용해 데이터를 관리 할 때, 특정 문자 또는 공백을 다른 문자로 일괄 교체 하거나 제거 하고 싶을 수 있습니다.
이 때 SUBSTITUTE 함수를 사용하면 손쉽게 텍스트 문자열에서 특정 문자를 다른 문자로 변경하거나 제거 할 수 있습니다.
그래서 오늘은 '엑셀'에서 SUBSTITUTE 함수로 공백을 제거하거나 특정 단어를 삭제 또는 교체하는 방법에 대해서 알아 봅니다.
튜토리얼 환경 : 윈도우 11, 마이크로소프트 365 엑셀 2022
해당 포스트는 충분한 테스트 및 검증 후 작성 되었지만 이것이 내용의 정확성이나 신뢰성에 대해 보증을 하는 것은 아니니 단순 하게 참고용으로 봐주시길 바랍니다.
SUBSTITUTE 구문
SUBSTITUTE 함수를 사용하기 전 기본적인 SUBSTITUTE 구문에 대해 정리합니다.
함수 인수 패널을 통해 기본적인 SUBSTITUTE 구문을 확인 할 수 있으며 실제 표기 되는 형식은 아래와 같습니다.
SUBSTITUTE(Text,Old_text,New_text,[Instance_num])
- Text: 문자를 대체할 텍스트가 포함된 셀의 참조 또는 텍스트 이며, 필수 요소입니다.
- Old_text: 바꿀 텍스트 이며, 필수 요소입니다.
- New_text: Old_text를 대신할 텍스트 이며, 필수 요소입니다.
- Instance_num: text 에서 몇 번째에 있는 Old_text를 New_text 로 바꿀 것 인지를 지정하는 수이며, 선택 요소입니다.
SUBSTITUTE 로 특정 문자 교체 또는 제거하기
SUBSTITUTE 를 사용 하면 다양한 방식으로 특정 문자 또는 공백을 교체 하거나 제거 할 수 있습니다.
특정 문자 교체하기
SUBSTITUTE 함수를 사용하면 아주 간단하게 특정 문자를 교체하는 기본적인 방법입니다.
기본 구문에 따라 =SUBSTITUTE(A2,"-","=") 과 같이 작성하면 A2 셀의 문자열에서 - 를 찾아 = 로 교체합니다.
공백 제거 또는 교체하기
기본적으로 앞서 설명한 문자를 제거하거나 교체하는 방식과 동일합니다.
다만, 엑셀에서 공백을 제거 하거나 교체 하기 위해서는 특정 문자 대신 공백이 포함 된 큰 따옴표 (" ") 와 공백이 없는 큰 따옴표 ("") 를 활용하면 됩니다.
특정 문자를 공백으로 교체하기
기본 구문에 따라 =SUBSTITUTE(A2,"-"," ") 과 같이 작성하면 A2 셀의 문자열에서 - 를 찾아 공백 으로 교체합니다.
공백 제거하기
기본 구문에 따라 =SUBSTITUTE(A2," ","") 과 같이 작성하면 A2 셀의 문자열에서 공백 을 찾아 값이 없는 상태로 제거 처리 됩니다.
특정 위치 이후 문자 교체하기
Instance_num 인수를 사용하면 대상 문자의 특정 순번 이후 문자를 교체하거나 제거 할 수 있습니다. 예를 들어 A 가 여러 개 포함 된 문자열에서 두 번째, 세 번째 또는 네 번째 이후에 표기 된 A 만을 대상으로 할 수 있습니다.
기본 구문에 따라 =SUBSTITUTE(A2,"-","&",2) 와 같이 작성하면 A2 셀의 문자열에서 - 를 & 로 교체 합니다. 다만 [Instance_num] 값이 2 로 설정 되어 있어 두 번째 부터 위치한 - 만을 대상으로 합니다. 만약 [Instance_num] 값이 3 으로 되어 있다면 세 번째 이후 값을 대상으로 합니다.
아스키 코드 활용(선택)
엑셀에서 텍스트를 인수로 사용하기 위해서는 큰 따옴표 ( "" )로 감싸 주어야 합니다. 이 경우 인수에 사용되는 문자 또는 기호와 중첩이 발생 할 수 있는데 이 때 아스키 코드를 사용하면 조금 더 확실하게 원하는 값을 인수로 지정할 수 있습니다.
아스키 코드 중 - 는 10 진수로 45 이며, 이 값을 CHAR() 과 함께 표기하면 동일한 문자로 인식됩니다. 10 진수 아스키 코드 값은 아스키 코드표에서 확인하세요.
위 아스키 코드로 기본 구문에 따라 =SUBSTITUTE(A2,CHAR(45),"=") 와 같이 작성하면 A2 셀의 문자열에서 - 를 찾아 = 로 교체합니다.
마무리
이렇게, '엑셀'에서 SUBSTITUTE 함수로 공백을 제거하거나 특정 단어를 삭제 또는 교체하는 방법에 대해 알아 보았습니다.
SUBSTITUTE 는 자료를 가공 할 때 자주 사용되는 함수 입니다. SUBSTITUTE 구문 자체가 간단해 처음 사용하는 경우에도 어렵지 않게 활용 할 수 있습니다.
엑셀에서 문자열로 된 데이터를 관리 하는 경우 해당 문자열에 공백을 추가하거나 특정 문자를 대량으로 변경 해야 하는 경우 앞서 설명한 SUBSTITUTE 함수를 활용 해 보시길 바랍니다.