목차▾
엑셀 SUBSTITUTE 함수란?
엑셀 SUBSTITUTE 함수는 텍스트 내에서 지정한 값을 찾아 새 값으로 치환하는 텍스트 함수입니다. 하이픈·공백·통화기호 제거, NBSP 정화, 다중 문자 순차 제거 등에 표준. REPLACE와 달리 값 기반이며 대소문자를 구분.
구문은 =SUBSTITUTE(text, old_text, new_text, [instance_num]). instance_num 생략 시 모두 치환, 지정 시 N번째만 치환.
SUBSTITUTE vs REPLACE
| 항목 | SUBSTITUTE | REPLACE |
|---|---|---|
| 지정 방식 | 값(텍스트) 지정 | 위치(숫자) 지정 |
| N번째만 | ✅ instance_num | ❌ |
| 모두 치환 | ✅ (기본) | ❌ |
| 대소문자 | 구분 | (위치 기반) |
| 주 사용 | 단어 교체, 하이픈 제거 | 주민번호 마스킹처럼 고정 위치 |
→ 값 알면 SUBSTITUTE, 위치 알면 REPLACE.
동작 예시
=SUBSTITUTE("abc-def-ghi", "-", "") → "abcdefghi"
=SUBSTITUTE("abc-def-ghi", "-", "|") → "abc|def|ghi"
=SUBSTITUTE("010-1234-5678", "-", "") → "01012345678"
=SUBSTITUTE("$1,234.56", "$", "") → "1,234.56"
=SUBSTITUTE("Hello", "l", "L") → "HeLLo" (모두)
=SUBSTITUTE("Hello", "L", "X") → "Hello" (대소문자 다름, 안 됨)
instance_num N번째만 치환
instance_num 인수로 N번째 발생만 치환.
=SUBSTITUTE("A-B-C-D", "-", " ", 1) → "A B-C-D"
=SUBSTITUTE("A-B-C-D", "-", " ", 2) → "A-B C-D"
=SUBSTITUTE("A-B-C-D", "-", " ", 3) → "A-B-C D"
=SUBSTITUTE("A-B-C-D", "-", " ") → "A B C D" (모두)
파일 경로 파싱 응용
마지막 / 위치 찾기 트릭:
=SUBSTITUTE(A1, "/", "§", LEN(A1)-LEN(SUBSTITUTE(A1, "/", "")))
→ / 총 개수만큼의 인덱스 = 마지막 / 를 § 로 치환
이후 FIND("§")로 위치 잡고 MID/RIGHT로 파일명 추출
실전 사용 시나리오 8가지
- 하이픈 제거 —
=SUBSTITUTE(A1, "-", "") - 공백 제거 —
=SUBSTITUTE(A1, " ", "")+ NBSP:=SUBSTITUTE(A1, CHAR(160), "") - 통화 기호 제거 —
=VALUE(SUBSTITUTE(SUBSTITUTE(A1, "$", ""), ",", "")) - 다중 치환 (중첩) — SUBSTITUTE 3중첩으로 여러 문자 순차 제거
- 매칭 개수 세기 —
=LEN(A1) - LEN(SUBSTITUTE(A1, "찾을값", "")) - 줄바꿈 문자 처리 —
=SUBSTITUTE(A1, CHAR(10), ", ") - TRIM + SUBSTITUTE —
=TRIM(SUBSTITUTE(A1, CHAR(160), " ")) - N번째만 치환 —
=SUBSTITUTE(A1, "-", "|", 2)
TRIM + CLEAN + SUBSTITUTE 콤보
웹·PDF에서 복사한 지저분한 텍스트 정화:
=TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " ")))
3단계 처리 (안쪽부터):
- SUBSTITUTE(A1, CHAR(160), " "): NBSP(웹 공백)를 일반 공백으로
- CLEAN: 인쇄 불가능 제어 문자 (CHAR(0)~CHAR(31)) 제거
- TRIM: 앞뒤 공백 + 중간 다중 공백 축소
→ 웹·PDF 복사 텍스트 한 방에 정화. NBSP(CHAR(160))는 일반 TRIM으로 안 잡힘 — 반드시 SUBSTITUTE로 변환.
확장 콤보 (여러 특수 문자)
=TRIM(CLEAN(
SUBSTITUTE(
SUBSTITUTE(A1, CHAR(160), " "),
CHAR(9), " ")))
→ NBSP + TAB 도 함께 처리
자주 발생하는 함정 6가지
- 대소문자 구분 — SUBSTITUTE는 구분 (SEARCH 반대). 무시하려면 UPPER/LOWER로 사전 통일
- 결과가 문자열 — 숫자로 쓰려면
VALUE(SUBSTITUTE(...)) - NBSP는 일반 공백 아님 — 웹은 CHAR(160), 일반은 CHAR(32). 별개 문자 명시 필요
- instance_num 초과 — 3개 있는데 5 지정하면 원본 그대로 (오류 X)
- REPLACE와 헷갈림 — 값 지정 = SUBSTITUTE, 위치 지정 = REPLACE
- 중첩 시 순서 중요 — 안쪽부터 실행. 잘못된 순서는 예상과 다른 결과
자주 묻는 질문
Q. SUBSTITUTE 함수 사용법은?
=SUBSTITUTE(원본, 찾을값, 새값, [instance_num]) 형식. 예: =SUBSTITUTE("A-B-C", "-", "") → "ABC". instance_num 생략 시 모두 치환, 지정하면 N번째만.
Q. SUBSTITUTE vs REPLACE 차이는?
SUBSTITUTE는 값 지정, REPLACE는 위치 지정. 단어 교체·하이픈 제거는 SUBSTITUTE, 주민번호 뒷자리 같은 고정 위치는 REPLACE.
Q. N번째만 치환은?
instance_num 인수 사용. =SUBSTITUTE("A-B-C-D", "-", "|", 2) → "A-B|C-D" (2번째만). 생략 시 모두 치환.
Q. 하이픈·공백 제거는?
하이픈: =SUBSTITUTE(A1, "-", "") (010-1234-5678 → 01012345678). 공백: =SUBSTITUTE(A1, " ", ""). NBSP는 =SUBSTITUTE(A1, CHAR(160), "").
Q. 대소문자 구분 없이 치환은?
SUBSTITUTE는 구분함. 무시하려면 UPPER/LOWER로 사전 통일: =SUBSTITUTE(UPPER(A1), "OLD", "NEW"). 또는 REGEX 함수(Excel 365의 REGEXREPLACE).
Q. 매칭 개수 세는 법은?
=LEN(A1) - LEN(SUBSTITUTE(A1, "찾을값", "")). 원본 길이 - 제거 후 길이 = 매칭 문자 수. 단어 개수는 / LEN("찾을값").
Q. 결과가 숫자 계산이 안 돼요.
SUBSTITUTE 결과는 문자열. =VALUE(SUBSTITUTE(A1, ",", "")) 또는 =--SUBSTITUTE(A1, ",", "") (이중 음수).
Q. 구글 스프레드시트에서도 동일하게 작동하나요?
네. SUBSTITUTE·REPLACE·TRIM·CLEAN 모두 표준 텍스트 함수로 Google Sheets, LibreOffice Calc, Apple Numbers, 한컴 한셀, WPS에서 동일하게 작동합니다.
본 페이지는 Microsoft Support 공식 SUBSTITUTE·REPLACE·TRIM 문서를 기준으로 작성되었습니다. 정확한 동작은 Excel 버전·환경에 따라 달라질 수 있으니 중요한 작업 전에는 실제 환경에서 확인하세요.