엑셀 SUBSTITUTE
텍스트 내용을 찾아 다른 텍스트로 바꾸는 함수 — N번째 매칭만 골라 치환도 가능
SUBSTITUTE 수식 구조
텍스트 (text)
치환할 원본 문자열. 셀 참조 또는 직접 텍스트
찾을값 (old_text)
바꿀 대상이 되는 문자/문자열. 대소문자 구분 (Apple ≠ apple)
바꿀값 (new_text)
새로 넣을 문자열. 빈 문자열 ""로 두면 삭제 효과
N번째 (instance_num) — 선택
생략 시 모두 치환. 숫자 지정 시 그 N번째 매칭만 치환 (나머지는 유지)
직접 체험해보세요
매칭된 문자열은 분홍색으로 강조됩니다. N번째 모드에서는 활성 매칭만 진한 색, 나머지는 회색으로
원본 (A2)
매칭된 2개 모두 치환
SUBSTITUTE 결과 (B2)
실전 활용 예제
=SUBSTITUTE(A2, "-", "")전화번호 하이픈 제거 — DB나 외부 시스템에 보낼 때 "010-1234-5678"을 "01012345678"로. 마지막 인수 생략 시 모든 "-" 제거.
=SUBSTITUTE(A2, CHAR(160), " ")NBSP를 일반 공백으로 — 웹에서 복사한 텍스트의 보이지 않는 NBSP(U+00A0)는 TRIM이 못 잡음. SUBSTITUTE로 일반 공백으로 변환 후 TRIM.
=SUBSTITUTE(A2, ".", ",", 2)2번째 점만 콤마로 — 1.234.567 같은 표기를 1.234,567로. N번째 인수의 가장 흔한 활용. 마지막 인수가 SUBSTITUTE의 핵심 차별점.
다른 엑셀 함수도 알아보세요
SUBSTITUTE vs REPLACE — 결정적 차이
▼
이름이 비슷하지만 동작 원리가 완전히 다릅니다. 잘못 골라 쓰면 결과가 엉뚱하게 나옵니다.
| 기준 | SUBSTITUTE | REPLACE |
|---|---|---|
| 방식 | 내용 매칭 | 위치 기반 |
| 예 | ("-", "") | (4, 1, "") |
| 대소문자 | 구분 ✓ | 해당 없음 |
| 적합 상황 | 하이픈·공백 제거 | 주민번호 마스킹 |
한마디로: "특정 문자열을 바꾸고 싶다" → SUBSTITUTE, "N번째 위치를 잘라 바꾸고 싶다" → REPLACE. 자세한 위치 기반 치환은 REPLACE 페이지.
N번째 매칭만 — instance_num의 진가
▼
SUBSTITUTE의 4번째 인수는 잘 알려지지 않은 강력한 기능입니다. 같은 문자가 여러 번 나올 때 특정 위치만 골라 치환할 수 있습니다.
예시 1 — 두 번째 점만 콤마로
SUBSTITUTE("a.b.c.d", ".", ",", 2)
→ "a.b,c.d"
예시 2 — 마지막 슬래시 뒤 추출 (파일명)
=MID(A2, FIND("\\\\", SUBSTITUTE(A2, "/", "\\\\", LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))) +1, 99)
LEN과 조합해 "/" 개수를 세고, 마지막 /만 특수문자(\)로 치환 후 FIND로 위치 찾기
매칭 수 세기 트릭: =LEN(A2)-LEN(SUBSTITUTE(A2,"단어",""))의 결과를 "단어"의 길이로 나누면 "단어"가 몇 번 나오는지 셀 수 있습니다 (간이 COUNTIF 대용).
데이터 정리 4종 콤보 — TRIM·CLEAN·SUBSTITUTE
▼
외부 데이터(CSV, 웹 스크래핑, 메일 본문)를 받으면 거의 항상 더러운 공백·기호가 섞입니다. 깨끗하게 만드는 정석 콤보:
단계별 클렌징
=TRIM(SUBSTITUTE(CLEAN(A2), CHAR(160), " "))
1. CLEAN — 인쇄 불가 문자(줄바꿈·탭 등) 제거
2. SUBSTITUTE CHAR(160) — NBSP를 일반 공백으로
3. TRIM — 앞뒤·중간 공백 정리
이 한 줄로 90% 케이스가 해결됩니다. TRIM 페이지에 TRIM이 못 잡는 케이스가 정리되어 있습니다.
자주 묻는 질문 (FAQ)
▼
Q. 대소문자를 무시하고 치환하고 싶어요
SUBSTITUTE는 대소문자를 구분합니다. 무시하려면 UPPER / LOWER로 통일 후 치환. 또는 정규식이 필요하면 VBA·파워 쿼리.
Q. 여러 문자를 한 번에 치환할 수 있나요?
SUBSTITUTE는 한 번에 한 종류씩. 여러 개면 중첩: =SUBSTITUTE(SUBSTITUTE(A2, "-", ""), ".", ""). Excel 365의 REGEXREPLACE가 등장하면 정규식으로 한 번에 가능.
Q. 찾을값이 없으면 어떻게 되나요?
오류 없이 원본 그대로 반환. 안전하게 체이닝할 수 있어 데이터 클렌징에 적합합니다.
Q. 워크시트의 Ctrl+H Find & Replace와는?
Ctrl+H는 값 자체를 영구 변경. SUBSTITUTE는 수식으로 결과 셀을 생성. 원본 유지가 필요하면 SUBSTITUTE, 일회성 정리면 Ctrl+H가 빠릅니다.