엑셀 SUBSTITUTE 함수: 치환 차수·접두사·마스킹 오류 검산
ABC-ABC에서 두 번째 ABC만 바꾸려면 문자 자체와 등장 순서를 함께 지정해야 합니다. 반대로 가상 회사명의 앞쪽 (주)만 지울 때는 중간의 같은 문자열을 보존해야 합니다. 두 일을 같은 일괄 치환으로 처리하면 원본 뜻이 달라질 수 있어, 이 실습은 치환·접두사·마스킹을 세 시트로 나눕니다.
도해의 가상 DEMO 문자열을 실습 파일의 치환·접두사·마스킹 시트와 대조하세요. 각 단계의 셀 주소는 아래 본문에 표시했습니다.
ABC-ABC를 바꾸는 네 번째 인수
치환 A:D는 원본·찾을 문자·바꿀 문자·차수입니다. D에 전체를 넣으면 네 번째 인수를 생략하고, 양의 정수를 넣으면 해당 등장만 바꿉니다. E는 입력 상태, F는 결과, G는 고정 기대값, H는 EXACT 대조입니다.
| 원본 | 찾기→교체 | 차수 | 기대 결과 |
|---|---|---|---|
| ABC-ABC | ABC→X | 전체 | X-X |
| ABC-ABC | ABC→X | 1 | X-ABC |
| ABC-ABC | ABC→X | 2 | ABC-X |
| abc-ABC | ABC→X | 전체 | abc-X |
| ABC | abc→X | 전체 | ABC |
| ABC-ABC | ABC→X | 3 | ABC-ABC |
-
치환A2:D7을 표와 대조합니다. 찾을 문자가 없거나 지정 차수가 등장하지 않으면 결과가 원본과 같을 수 있습니다. 이를 치환 성공의 증거로 삼지 않습니다. -
E열이
정상인 행의 F열을 확인합니다. 차수 전체와 숫자는 별도 분기에서 처리해전체라는 문자를 INT로 계산하지 않습니다. -
H열 TRUE와 원본·결과를 함께 봅니다. 바꿀 문자를 원래 문자와 똑같이 넣은 행도 결과가 같으므로, 실제 작업에서는 변경 의도가 맞는지 별도로 검토합니다.
=IF(E2<>"정상","",IF(D2="전체",SUBSTITUTE(A2,B2,C2),SUBSTITUTE(A2,B2,C2,D2)))SUBSTITUTE는 텍스트 안의 old_text를 new_text로 바꿉니다. 이 예제는 ABC와 abc를 구분하며 별표도 와일드카드가 아닌 문자로 취급합니다. A*B*C에서 별표를 빈 문자열로 교체한 F11은 ABC입니다.
위치를 지정할 때는 REPLACE로 비교하기
치환 B14의 =REPLACE("ABC-ABC",4,3,"X")는 4번째 하이픈부터 세 글자 -AB를 바꿔 ABCXC가 됩니다. B15의 시작 위치를 5로 바꾼 수식은 ABC-X입니다. REPLACE 문서처럼 위치와 길이를 지정하는 방식이므로 SUBSTITUTE의 “두 번째 등장”과 다릅니다.
B16의 =SUBSTITUTE(12500,"0","X") 결과는 문자 125XX입니다. 이후 금액 합산에 그대로 쓸 숫자가 아니므로 표시용 변환 열과 원본 수치 열을 분리하세요.
앞에 있을 때만 회사명 접두사를 제거하기
접두사 A·B·C열에는 가상 원본, 허용 접두사, 수동 승인 문자열이 있습니다. (주)가상(주)연구소에서 앞부분만 지우면 가상(주)연구소가 남아야 합니다. 가상(주)연구소처럼 중간에만 있는 문자열은 그대로입니다.
D열은 EXACT(LEFT(원본,LEN(접두사)),접두사)를 사용합니다. 승인된 정상 입력에 한해 앞이 일치하면 REPLACE로 그 길이만 제거합니다. 결과는 F열의 검토 상태이며 원본을 덮어쓰거나 고유 키 변경을 승인하는 기능은 아닙니다.
가상 입력 ㈜가상제작은 접두사 ㈜와, 주식회사 가상기술은 끝 공백을 포함한 접두사 주식회사 와 각각 비교합니다. 서로 다른 표기를 하나의 규칙으로 추정하지 않습니다. 미승인은 승인보류, 빈 접두사는 입력보류, 원본이 (주)뿐이라 결과가 없어지면 빈결과보류입니다.
마스킹 시작 위치가 한 칸 밀리면 남는 문자
마스킹 A2의 DEMOAA-ABCDEFG는 실존 식별자가 아닌 14글자 연습 문자열입니다. 7번째 글자가 하이픈 하나이고 전체 길이가 14일 때만 B열이 정상입니다. 뒤 일곱 글자는 8번째에서 시작합니다.
=IF(B2="정상",REPLACE(A2,8,7,"*******"),"")C2는 DEMOAA-*******이고 길이는 14입니다. 반면 F2는 일부러 잘못된 9번째 시작을 사용해 DEMOAA-A*******가 됩니다. 표시상 앞의 A 하나가 그대로 남으며 총길이도 달라집니다. MID 설명을 읽을 때는 시작 위치를 원본에서 직접 세어 보세요. 짧은 문자열, 밑줄 구분자, 하이픈 두 개, 빈 원본은 모두 형식보류입니다.
실패를 재현하고 원래 규칙으로 복구하기
치환 D4를 2에서 3으로 바꾸면 F4는 ABC-ABC, H4는 FALSE입니다. 세 번째 등장이 없기 때문입니다. D4를 2로 되돌리면 ABC-X와 TRUE로 복구됩니다. 찾을 문자열을 비우거나 차수를 0으로 넣은 행은 규칙보류를 보여야 합니다.
접두사 C2를 미승인으로 바꾼 사본은 결과가 빈 표시이고 승인보류입니다. 승인 근거가 있을 때만 다시 승인으로 입력합니다. B2를 비운 경우에는 먼저 (주)를 복구합니다. 마스킹 원본을 DEMOAA-1234567로 바꿔도 올바른 결과는 DEMOAA-*******이며 잘못된 9번째 시작식에는 1이 남습니다. 이는 특정 영문 입력에만 맞춘 우연한 결과가 아님을 확인하는 반례입니다.
치환 뒤에도 눈에 보이지 않는 문자가 남는 경우는 CLEAN으로 숨은 문자와 공백 종류를 구분하는 실습에서 이어서 확인할 수 있습니다. 탭·NBSP·일반 공백을 먼저 구분하고, 코드와 문장의 정리 규칙을 따로 검산합니다.
표시 정리와 보안 처리를 혼동하지 않는 한계
이 첨부는 원본 보존·문자 위치·수동 승인·형식 검사를 연습합니다. 마스킹은 암호화나 익명화를 보장하지 않으며 원본 셀을 포함한 파일은 민감 자료 공유용으로 적합하지 않습니다. 실제 개인정보를 넣지 마세요. 접두사 제거 후 다른 이름과 충돌하는지도 별도 검사해야 하며, 행 수나 허용 표기를 늘리면 기대값을 새로 작성해 대조해야 합니다.
REPLACE는 지정한 문자 수에 따라 텍스트의 일부를 다른 문자열로 바꿉니다.
MID는 지정한 위치에서 시작하는 일정 수의 문자를 반환합니다.
출처 원문 수집일: S1 2026-09-24; S2 2026-09-24; S3 2026-09-24.
작성자 소개
