엑셀 SUBSTITUTE 함수는 텍스트에서 특정 문자열을 다른 문자열로 변경하는 함수입니다. 문자 기준 변경으로 데이터 정제에 매우 자주 사용됩니다. REPLACE와는 접근 방식이 다른 중요한 함수입니다.
인수는 대상 텍스트, 찾을 문자열, 바꿀 문자열, N번째 등장(선택)입니다. 매우 유연한 텍스트 변경이 가능합니다.
SUBSTITUTE 기본
SUBSTITUTE(“텍스트”, “찾을것”, “바꿀것”)로 사용합니다. 대상 텍스트의 모든 찾을 것이 바꿀 것으로 변경됩니다.
대소문자를 구분해 정확한 매칭만 변경됩니다.
찾을 것이 없으면 원본이 그대로 반환됩니다. 오류가 발생하지 않아 안전합니다.
N번째만 변경
네 번째 인수로 몇 번째 등장을 변경할지 지정할 수 있습니다. 지정하지 않으면 모두 변경됩니다.
SUBSTITUTE(텍스트, 찾을것, 바꿀것, 2)는 두 번째 등장만 변경합니다. 정밀한 제어가 가능합니다.
여러 등장 중 특정 위치만 변경할 때 매우 유용합니다.
공백 제거
모든 공백 제거는 SUBSTITUTE(텍스트, ” “, “”)로 처리합니다. 매우 자주 사용되는 정제 작업입니다.
전화번호의 하이픈 제거, 숫자의 콤마 제거 같은 다양한 정제에 활용됩니다.
TRIM과 함께 사용하면 종합적인 텍스트 정제가 가능합니다.
REPLACE와 차이
SUBSTITUTE는 문자 기준, REPLACE는 위치 기준으로 변경합니다.
변경 대상을 알면 SUBSTITUTE, 위치를 알면 REPLACE가 적합합니다.
대부분의 실무에는 SUBSTITUTE가 더 자주 사용됩니다.
중첩 활용
여러 문자를 순차 변경하려면 SUBSTITUTE를 중첩합니다. 하이픈, 공백, 콤마를 모두 제거할 수 있습니다.
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(텍스트, “-“, “”), ” “, “”), “,”, “”) 같은 패턴입니다.
복잡한 변경은 REGEXREPLACE가 더 간결하지만 M365 전용입니다.
자주 묻는 질문
Q1. SUBSTITUTE 기본 사용은?
SUBSTITUTE(텍스트, 찾을것, 바꿀것)로 모든 등장을 변경합니다.
Q2. 특정 등장만 변경은?
네 번째 인수로 등장 순서를 지정합니다.
Q3. 공백 모두 제거는?
SUBSTITUTE(텍스트, ” “, “”)로 처리합니다.
Q4. REPLACE와 차이는?
SUBSTITUTE는 문자 기준, REPLACE는 위치 기준입니다.
Q5. 여러 문자 동시 변경?
SUBSTITUTE를 중첩하거나 M365의 REGEXREPLACE를 사용합니다.
정리
엑셀 SUBSTITUTE는 텍스트의 특정 문자열을 다른 문자열로 변경하는 매우 유용한 함수입니다. 문자 기준 변경으로 데이터 정제에 필수적입니다.
공백 제거, 형식 통일, 특수 문자 처리 같은 다양한 실무에 매우 효과적입니다. N번째 등장만 변경하는 정밀 제어와 중첩 활용으로 매우 강력한 텍스트 정제가 가능합니다.