SUBSTITUTE 함수: 구문, 예제 및 오류 해결
2026-10-13 업데이트 · 무료 · Excel 및 Google Sheets 지원
구문
=SUBSTITUTE(text, old_text, new_text, [instance_num])빠른 답변
SUBSTITUTE는 나타나는 모든 곳에서 텍스트 조각을 다른 텍스트로 바꿉니다: =SUBSTITUTE(A2, "-", " ")는 "555-01-2345"를 "555 01 2345"로 만듭니다. 대소문자를 구별하고, instance_num을 생략하면 전부 바꾸며, instance_num이 0이면 #VALUE!를 반환합니다.
구문
=SUBSTITUTE(text, old_text, new_text, [instance_num])| 인수 | 의미 |
|---|---|
| text | 검색할 셀 또는 리터럴. 필수. |
| old_text | 바꿀 정확한 텍스트. 일치 판정은 대소문자를 구별하고 문자 그대로 — 와일드카드는 안 됩니다. 필수. |
| new_text | 바꿔 넣을 내용. 빈 문자열 ""은 일치 항목을 통째로 지웁니다. 필수. |
| [instance_num] | 바꿀 발생 위치: 3이면 세 번째만 변경. 생략하면 모두 바뀝니다. 1 이상이어야 합니다. |
국가별 설정에 따라 인수가 쉼표 대신 세미콜론으로 구분됩니다: =SUBSTITUTE(A2; "-"; " "). 쉼표가 소수점 기호인 국가의 표준 동작입니다.
모두를 한 번씩 놀라게 하는 두 가지 동작: 일치 판정은 대소문자를 구별하고("ID"와 "id"는 다른 표적), instance_num 없이는 바꾸기가 모든 발생에 적중합니다 — "첫 번째만 바꾸기" 스위치는 없습니다.
기본 예제
=SUBSTITUTE(A2, "-", " ")하이픈을 공백으로: 하이픈으로 쓰인 전화번호·부품 코드·ID 번호가 한 번에 읽기 좋아집니다. 모든 하이픈이 바뀝니다 — 수식은 셀 전체에서 바꾸기를 반복합니다.
=SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", "")문자 삭제: new_text의 ""는 일치 항목을 흔적 없이 지웁니다. SUBSTITUTE를 두 겹으로 중첩하면 수식 하나로 "(555) 0102"의 양쪽 괄호를 모두 벗깁니다 — 지우고 싶은 문자 종류마다 한 겹씩.
=SUBSTITUTE(A2, "/", "-", 2)instance_num 인수는 단일 발생에 고정합니다: 두 번째 슬래시만 하이픈으로 바뀌고 첫 번째와 세 번째는 그대로입니다. 발생은 텍스트 시작점에서 왼쪽부터 오른쪽으로 셉니다.
=LEN(A2)-LEN(SUBSTITUTE(A2, " ", ""))+1고전적인 단어 수 세기: 공백을 모두 지우고 두 길이를 비교하면 공백 개수가 나오고, +1이 그것을 단어 수로 바꿉니다. VBA도 분석 도구도 없이 — 수식 하나.
SUBSTITUTE의 결과는 텍스트입니다 — 숫자처럼 보여도. "1 234"를 정리한 "1234"는 합산·평균·차트가 되지 않습니다. 감쌉니다: =VALUE(SUBSTITUTE(A2, " ", ""))가 정리된 텍스트를 진짜 숫자로 되돌립니다.
자주 발생하는 오류와 해결 방법
| 증상 | 가장 흔한 원인 | 해결 |
|---|---|---|
| 아무것도 바뀌지 않음 | 대소문자 불일치 — "ID"는 "id"에 맞지 않음 — 또는 보이지 않는 후행 공백이 "abc "와 "abc"를 다른 것으로 만듦. | 대소문자를 완전히 맞추거나, 먼저 TRIM으로 원본을 정리하거나, 찾기 및 바꾸기 대화상자를 쓰세요 — 거기서는 대소문자 일치가 체크박스입니다. |
| #VALUE! | instance_num이 0 또는 음수. | 1 이상을 쓰거나 — 인수를 아예 생략해 모두 바꿉니다. |
| 잘못된 위치가 바뀜 | 발생은 왼쪽부터 오른쪽으로 세어지고, instance 2가 남겨두려던 일치에 적중함. | 먼저 LEN 기술로 발생 수를 세거나, 앞뒤 문자를 포함해 표적을 유일하게 만듭니다: =SUBSTITUTE(A2, "/2024/", "/2025/")는 이 패턴 안의 슬래시만 적중합니다. |
| 예상보다 많이 바뀜 | 짧은 old_text가 긴 단어 내부에도 일치: "1"을 바꾸면 "12"와 "21"도 맞음. | 앞뒤 문자를 old_text에 포함하거나, instance_num으로 발생 위치를 고정합니다. |
| 숫자가 계산되지 않음 | 결과는 텍스트입니다 — 숫자처럼 보여도. | 전체를 VALUE로 감싸거나, 계산에는 원본 셀을 계속 씁니다. |
또 하나의 조용한 헛동작: instance_num이 실제 발생 수를 넘으면 SUBSTITUTE는 원본 텍스트를 그대로 반환합니다 — 오류도 힌트도 없이. 수식이 "아무것도 안 한다"면 철자를 의심하기 전에 일치 수부터 세세요.
SUBSTITUTE vs REPLACE
| SUBSTITUTE | REPLACE | |
|---|---|---|
| 찾는 방식 | 텍스트 자체 — 셀 안 어디에 있든 | 고정 위치: 시작 위치 + 문자 수 |
| 쓰는 때 | 무엇을 바꿀지는 알지만 어디인지 모를 때 | 정확한 위치와 길이를 알 때 |
| 범위 | 모든 발생 — instance_num으로 하나만 고정 가능 | 호출당 고정 블록 하나 |
| 예제 | =SUBSTITUTE(A2, "-", " ") | =REPLACE(A2, 4, 2, "XX") |
REPLACE는 고정 위치의 문자를 바꿉니다 — "항상 4~5번째 문자를 덮어쓰기"처럼 레이아웃이 딱딱할 때 맞는 도구입니다. 표적 텍스트가 셀 어디든 나타날 수 있는 순간 SUBSTITUTE가 이어받습니다 — 위치가 아니라 텍스트를 사냥하기 때문입니다.
SUBSTITUTE vs 찾기 및 바꾸기
| SUBSTITUTE | 찾기 및 바꾸기(Ctrl+H) | |
|---|---|---|
| 존재 형태 | 수식 — 결과가 자기 셀에 놓이고 원본은 무사 | 대화상자 — 셀을 그 자리에서 고쳐 원본을 덮어씀 |
| 재계산 | 함 — 원본이 바뀌면 바꾸기 결과도 따라감 | 아니오 — 일발성. 새 데이터는 재실행 |
| 반복 범위 | 아래로 채우기로 수식 하나가 수천으로 | 통합 문서 전체를 한 번에 |
| 대소문자 처리 | 항상 구별함 | 기본은 구별하지 않음, 대/소문자 구분 체크박스 있음 |
실무 분업: 시트에 이미 있는 것의 일회성 청소는 찾기 및 바꾸기로, 청소가 새 데이터 — 가져오기, 새로 고침, 대량 붙여넣기 — 를 견뎌야 하면 SUBSTITUTE로. 많은 시트가 원본 가져오기 열을 먹는 SUBSTITUTE 열로 귀결됩니다.
Google Sheets에서 SUBSTITUTE
같은 이름, 같은 네 인수, 같은 대소문자 구별 문자 일치 — 위의 수식은 그대로 붙여 넣을 수 있습니다. Sheets는 같은 발상을 강력한 도구로도 만들었습니다: REGEXREPLACE는 정규식을 받아 수식 하나로 엉망인 형식을 한꺼번에 정규화합니다.
- 쉼표를 소수점으로 쓰는 지역에서는 세미콜론으로: =SUBSTITUTE(A2; "-"; " ").
- Sheets의 찾기 및 바꾸기 대화상자(Ctrl+H)에는 Excel에 없는 "정규 표현식 사용" 옵션이 있습니다.
- 문자 삭제 시 new_text의 ""는 Excel과 완전히 같은 동작 — 흔적이 남지 않습니다.
한계와 고급 팁
- 호출당 old_text는 하나: 여러 종류의 문자를 청소하려면 중첩 — 한 겹에 한 종류씩 벗깁니다.
- 패턴이 겹치면 순서가 중요: ":"보다 "::"를 먼저 바꿉니다 — 그렇지 않으면 단일 콜론 규칙이 돌 때쯤엔 이중 콜론이 이미 사라졌습니다.
- 와일드카드 없음 — old_text는 문자 그대로 일치하고 "*"와 "?"는 진짜 별표와 물음표를 찾습니다. 패턴의 화력은 찾기 및 바꾸기에, Sheets에선 REGEXREPLACE에 있습니다.
- 줄바꿈은 CHAR(10): =SUBSTITUTE(A2, CHAR(10), " ")로 줄바꿈 셀을 한 줄로 눕힙니다 — 필요하면 그 후 진짜 숫자로 변환합니다.
- 재사용 가능한 청소 파이프라인: SUBSTITUTE가 잡음을, TRIM이 잔여를, VALUE가 숫자를 — 세 열이 매번의 재가져오기를 견딥니다.
SUBSTITUTE를 가져온 시트의 면역계로 다루세요: 원본 데이터는 더러운 채 착지하고, substitute 열이 착지와 동시에 청소하며, 아래쪽 수식은 모두 깨끗한 버전을 먹습니다 — 누구도 원본 열을 손으로 고치지 않게 됩니다.
관련 도구
관련 함수
자주 묻는 질문
SUBSTITUTE는 Excel에서 무엇을 하나요?
텍스트 조각을 나타나는 모든 곳에서 다른 텍스트로 바꿉니다: =SUBSTITUTE(A2, "-", " ")는 모든 하이픈을 공백으로 바꿉니다. 선택적 instance_num 인수로 특정 발생 한 곳만 바꾸게 할 수도 있습니다.
SUBSTITUTE는 대소문자를 구별하나요?
구별합니다 — old_text는 대소문자까지 정확히 일치해야 하고 대소문자 무시 스위치는 없습니다. 일회성 대소문자 무시 청소는 찾기 및 바꾸기 대화상자를 쓰세요. 거기는 대/소문자 구분 체크박스 방식입니다.
두 번째 발생만 바꾸려면?
네 번째 인수에 2를 넣습니다: =SUBSTITUTE(A2, "/", "-", 2). 발생은 왼쪽부터 오른쪽으로 세고 다른 일치는 그대로입니다. 1 이상 아무 숫자나 되고, 0이나 음수는 #VALUE!를 반환합니다.
셀에서 공백을 모두 제거하려면?
아무것도 아닌 것으로 바꿉니다: =SUBSTITUTE(A2, " ", ""). 빈 new_text가 셀 안의 모든 공백을 지웁니다. 숫자처럼 보이는 결과는 이후 VALUE로 감싸 정리된 텍스트가 다시 계산되게 합니다.
SUBSTITUTE와 REPLACE의 차이는?
SUBSTITUTE는 어디든 텍스트를 사냥하고, REPLACE는 고정 위치와 길이를 덮어씁니다(=REPLACE(A2, 4, 2, "XX")). 무엇을 바꿀지 알면 SUBSTITUTE, 정확히 어디인지 알면 REPLACE입니다.
SUBSTITUTE가 #VALUE!를 반환하는 이유는?
네 번째 인수 instance_num이 0 또는 음수이기 때문입니다. 1 이상이거나, 모든 발생을 바꾸려면 아예 생략해야 합니다. 실제 발생 수를 넘는 것은 오류가 아닙니다 — 텍스트가 그대로 돌아올 뿐입니다.
SUBSTITUTE는 Google Sheets에서 되나요?
됩니다 — 이름·인수·대소문자 구별 문자 일치가 모두 같습니다. Sheets는 패턴 기반 바꾸기의 REGEXREPLACE와 정규식 지원 찾기 및 바꾸기 대화상자를 추가로 제공합니다.
내 파일로 직접 해볼 준비가 되셨나요?
Excel 일괄 찾기 및 바꾸기 열기 — 무료