예를 들어, 상품코드의 일부 문자를 수정하거나, 고객명에 포함된 기호를 제거해야 하는 경우다.
이럴 때 유용하게 쓸 수 있는 함수가 바로 SUBSTITUTE 함수다.
이 함수는 셀 안의 특정 문자나 숫자를 지정한 값으로 바꿔주는 기능으로,
복잡한 데이터 정리 작업을 간단히 처리할 수 있다.
이번 글에서는 SUBSTITUTE 함수의 구조부터 실무 예제, REPLACE와의 차이까지
한눈에 이해할 수 있도록 정리했다.
엑셀 SUBSTITUTE 함수 사용법, 특정 문자만 골라 바꾸는 기술
엑셀을 사용하다 보면 데이터 안의 특정 문자나 숫자를 다른 값으로 바꿔야 하는 경우가 많다.
예를 들어, 고객명 중 “㈜” 표기를 없애야 하거나, 상품코드의 일부 숫자를 수정해야 할 때가 그렇다.
이럴 때 가장 유용하게 쓸 수 있는 함수가 바로 SUBSTITUTE 함수다.
이 함수는 셀 내의 문자를 지정한 대로 다른 문자로 교체할 수 있어, 데이터 정리 작업의 시간을 크게 단축시켜 준다.
SUBSTITUTE 함수의 기본 구조
SUBSTITUTE 함수의 기본 형식은 다음과 같다.
=SUBSTITUTE(대상_셀, "기존문자", "새문자", [선택요소])
- 대상_셀 : 문자 교체가 일어날 셀 주소
- 기존문자 : 바꾸고 싶은 문자
- 새문자 : 새로 대체할 문자
- 선택요소(선택사항) : 바꿀 문자의 순서를 지정 (입력하지 않으면 모든 항목 변경)
예를 들어, A2 셀에 SKT고객센터라는 문구가 있다면,
=SUBSTITUTE(A2, "SKT", "KT")
를 입력하면 결과는 KT고객센터가 된다.
💡 핵심 요약: SUBSTITUTE 함수는 셀 내부의 특정 문자를 새로운 문자로 대체하는 함수다.
SUBSTITUTE 함수의 선택 요소 이해하기
네 번째 인수인 [선택요소]는 SUBSTITUTE의 중요한 특징이다.
이 부분을 지정하지 않으면 모든 동일 문자가 한 번에 바뀌지만, 숫자를 입력하면 그 순서의 문자만 교체된다.
| 예시 | 수식 | 결과 |
|---|---|---|
| 전체 바꾸기 | =SUBSTITUTE(A3,”0″,”5″) | 모든 0이 5로 변경 |
| 첫 번째만 바꾸기 | =SUBSTITUTE(A3,”0″,”5″,1) | 첫 번째 0만 5로 변경 |
| 두 번째만 바꾸기 | =SUBSTITUTE(A3,”0″,”5″,2) | 두 번째 0만 5로 변경 |
즉, 선택요소를 활용하면 같은 문자라도 위치별로 선택적 교체가 가능하다.
문자열 안에 반복되는 문자가 많을 때 유용한 기능이다.
💡 핵심 요약: 선택요소를 입력하면 특정 위치의 문자만 골라서 교체할 수 있다.
SUBSTITUTE 함수로 문자와 숫자 동시에 바꾸기
엑셀 SUBSTITUTE 함수는 문자뿐 아니라 숫자도 교체할 수 있다.
숫자를 문자열로 인식하기 때문에, 데이터 형식에 상관없이 같은 방식으로 적용된다.
예를 들어, 상품 코드에 포함된 숫자를 바꾸려면 다음과 같이 입력한다.
=SUBSTITUTE(B2, "01", "05")
B2 셀의 값이 ABC01이라면 결과는 ABC05가 된다.
이 방법은 코드 수정이나 일련번호 보정 시 자주 활용된다.
💡 핵심 요약: SUBSTITUTE는 문자와 숫자 모두 문자열로 인식해 동일하게 처리할 수 있다.
SUBSTITUTE 함수 실전 예제
회사명 통일하기
데이터 표 안에 “주식회사”와 “㈜”가 섞여 있다면 다음 수식을 이용할 수 있다.
=SUBSTITUTE(A2,"㈜","주식회사")
이렇게 하면 전체 데이터에서 회사명이 일관되게 표시된다.
상품명 내 단위 제거
A3 셀에 “500ml 커피”가 있다면
=SUBSTITUTE(A3,"ml","")
결과는 “500 커피”가 되어 단위 표기만 제거된다.
여러 개의 다른 문자 바꾸기
한 번에 여러 문자 조합을 바꾸고 싶다면 SUBSTITUTE 함수를 중첩해 사용한다.
=SUBSTITUTE(SUBSTITUTE(A2,"/","-"),".","")
이 수식은 /를 -로 바꾸고, 동시에 .은 제거한다.
날짜 형식을 정리하거나 파일명에서 특수문자를 제거할 때 자주 사용된다.
💡 핵심 요약: SUBSTITUTE는 중첩 사용으로 다중 문자 교체도 가능하다.
SUBSTITUTE와 REPLACE의 차이점
SUBSTITUTE와 비슷한 역할을 하는 함수로 REPLACE 함수가 있다.
하지만 두 함수는 작동 방식이 다르다.
| 구분 | SUBSTITUTE | REPLACE |
|---|---|---|
| 교체 기준 | 기존 문자의 ‘내용’ | 기존 문자의 ‘위치’ |
| 주요 용도 | 특정 문자, 단어 변경 | 일정 위치 이후 문자 교체 |
| 예시 | =SUBSTITUTE(A1,”A”,”B”) | =REPLACE(A1,1,2,”B”) |
즉, SUBSTITUTE는 단어를 기준으로 문자를 찾아 바꾸고,
REPLACE는 몇 번째 문자부터 몇 개를 바꿀지를 기준으로 작동한다.
텍스트 분석이나 문장 정리에는 SUBSTITUTE가 훨씬 직관적이다.
💡 핵심 요약: SUBSTITUTE는 ‘내용 기반 교체’, REPLACE는 ‘위치 기반 교체’ 함수다.
SUBSTITUTE 함수 활용 시 주의할 점
- 대소문자 구분 없음
SUBSTITUTE 함수는 기본적으로 대소문자를 구분하지 않는다. “ABC”와 “abc”는 동일한 문자로 인식된다.
- 공백도 문자로 인식됨
” ” (공백)을 바꾸면 문장 내 공백 제거가 가능하다.
=SUBSTITUTE(A1," ","")
- 숫자 서식이 깨질 수 있음
숫자 데이터에 SUBSTITUTE를 적용하면 형식이 텍스트로 바뀔 수 있다. 이 경우 **VALUE 함수**로 다시 숫자 형식으로 변환하면 된다.
💡 핵심 요약: SUBSTITUTE는 문자의 형태를 기준으로 동작하며, 공백·대소문자 모두 인식한다.
마무리 요약
SUBSTITUTE 함수는 엑셀 데이터 정리의 핵심 도구다.
문자와 숫자를 구분하지 않고 바꿀 수 있으며, 반복되는 문자의 특정 위치만 선택적으로 교체할 수 있다.
또한, REPLACE 함수보다 직관적이어서 텍스트 중심의 데이터 수정에 더 적합하다.
이 함수를 활용하면 대량의 데이터에서 특정 문자를 빠르고 정확하게 변경할 수 있어,
실무 효율성이 눈에 띄게 향상된다.
마무리하며,
SUBSTITUTE 함수는 엑셀 데이터 정리의 핵심 도구다. 문자와 숫자를 구분하지 않고 바꿀 수 있으며, 반복되는 문자의 특정 위치만 선택적으로 교체할 수 있다. 또한, REPLACE 함수보다 직관적이어서 텍스트 중심의 데이터 수정에 더 적합하다. 이 함수를 활용하면 대량의 데이터에서 특정 문자를 빠르고 정확하게 변경할 수 있어, 실무 효율성이 눈에 띄게 향상된다.