엑셀 SUBSTITUTE 함수 – 원하는 문자만 정확하게 바꾸는 가장 강력한 방법
엑셀 작업을 하다 보면 텍스트 안에 있는 특정 문자 또는 문자열을 다른 내용으로 바꿔야 하는 경우가 매우 자주 발생합니다. 예를 들어:
- 전화번호에서 하이픈(-)을 제거하고 숫자만 남기고 싶을 때
- 상품 코드에서 특정 문자만 변경해야 할 때
- 주소에서 “서울특별시”를 “서울시”로 일괄 변경할 때
- 데이터에 섞인 특수문자를 자동으로 지우고 싶을 때
이럴 때 가장 정확하고 강력한 함수를 바로 SUBSTITUTE 함수입니다. REPLACE 함수와 비슷해 보이지만, SUBSTITUTE는 “문자 위치가 불규칙해도 특정 문자만 찾아 교체하는 기능”이라 실무에서는 훨씬 더 자주 사용되는 함수입니다.
1. SUBSTITUTE 함수란?
텍스트 문자열에서 원하는 문자 또는 단어(문자열)를 찾아 다른 문자로 변경하는 기능을 제공하는 텍스트 함수입니다.
기본 구조
=SUBSTITUTE(텍스트, 찾을_문자, 바꿀_문자)아주 단순하지만, 실무에서는 수십 가지 활용이 가능합니다.
2. SUBSTITUTE 함수 기본 예제
① 특정 문자 제거
A1 = "010-2222-3333"
하이픈(-)을 모두 없애려면:
=SUBSTITUTE(A1, "-", "")결과 → 01022223333
② 단어 교체
A1 = "서울특별시 강남구"
=SUBSTITUTE(A1, "서울특별시", "서울시")결과 → "서울시 강남구"
③ 특정 문자만 선택적으로 변경(중요)
SUBSTITUTE의 강력한 기능 중 하나는 "N번째 문자만 바꾸기" 기능입니다.
형식은 다음과 같습니다:
=SUBSTITUTE(텍스트, 찾을_문자, 바꿀_문자, N번째)예를 들어 A1 = "aaa" 일 때, 두 번째 a만 b로 바꾸려면:
=SUBSTITUTE(A1, "a", "b", 2)결과 → "aba"
3. SUBSTITUTE 함수가 필요한 실무 상황
① 전화번호에서 하이픈 제거
| 원본 | 결과 |
|---|---|
| 010-1234-9999 | =SUBSTITUTE(A1,"-","") → 01012349999 |
휴대전화 번호를 숫자로만 저장해야 하는 금융·고객 DB 처리에서 필수입니다.
② 상품 코드 정리
A1 = “PRD-001-A”에서 하이픈만 제거:
=SUBSTITUTE(A1,"-","")결과 → PRD001A
③ 주소 데이터 표준화
“서울특별시” → “서울시”
=SUBSTITUTE(A1, "서울특별시", "서울시")주소/지역 데이터 정규화 시 가장 많이 사용됩니다.
④ 줄바꿈 문자(LF) 제거
특히 외부 시스템 데이터의 가장 흔한 문제.
=SUBSTITUTE(A1, CHAR(10), "")줄바꿈 완전 제거
4. SUBSTITUTE + TRIM + CLEAN = 실무 최강 조합
텍스트 데이터를 깨끗하게 정리해야 한다면 단독 SUBSTITUTE보다 조합이 훨씬 강력합니다.
① 공백 + 특수문자 + 특정 문자 제거
=TRIM(CLEAN(SUBSTITUTE(A1, "-", "")))전화번호, 코드값, 아이디 등 불필요 데이터를 제거할 때 최강 조합입니다.
② 공백이 아닌 특수 공백(CHAR160) 삭제
TRIM 또는 CLEAN으로 제거되지 않는 “악성 공백” 제거:
=SUBSTITUTE(A1, CHAR(160), "")③ 한 문자열에서 여러 문자 동시에 제거
공백 + 괄호 + 하이픈 제거 예:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, " ", ""), "-", ""), "(", "")SUBSTITUTE는 중첩이 가능해 복잡한 문자 처리에 매우 강합니다.
5. 실무에서 가장 자주 쓰는 SUBSTITUTE 공식 모음
① 숫자만 남기기(문자 제거)
숫자가 아닌 모든 문자 제거는 SUBSTITUTE 단독으로는 어렵지만 하이픈, 공백 정도 제거는 매우 쉽습니다.
=SUBSTITUTE(SUBSTITUTE(A1,"-","")," ","")② 이름 뒤 직급 제거
A1 = "홍길동 과장"
=SUBSTITUTE(A1," 과장","")③ 특정 단어 강조 삭제
A1 = "★베스트상품★ 할인중"
=SUBSTITUTE(A1,"★","")결과 → 베스트상품 할인중
6. SUBSTITUTE 함수 vs REPLACE 함수 차이
| 함수 | 기능 특성 | 언제 사용? |
|---|---|---|
| SUBSTITUTE | 특정 글자를 찾아 교체 | 문자의 위치가 일정하지 않을 때 |
| REPLACE | 문자열의 시작 위치와 길이를 지정해 교체 | 제품코드처럼 위치가 고정된 경우 |
실무에서 SUBSTITUTE 사용 빈도가 훨씬 높습니다.
정리
SUBSTITUTE 함수는 엑셀에서 텍스트 조작을 위해 가장 많이 사용되는 핵심 함수 중 하나입니다. 특정 문자만 정확하게 제거하거나 교체해야 하는 모든 상황에서 가장 간단하면서 정확한 해결책을 제공합니다.
TRIM, CLEAN과 함께 사용하면 데이터 전처리 품질이 크게 향상되며 특히 전화번호, 주소, 상품코드 등 텍스트 기반 데이터의 자동화 작업에서 필수 도구라고 할 수 있습니다.
다음 글에서는 SUBSTITUTE 이후 자연스럽게 이어지는 LEFT·RIGHT·MID — 텍스트 원하는 부분만 추출하기 주제를 작성해드릴까요?
계속 이어서 작성해드릴까요?
0 댓글