엑셀 substitute, relace 함수로 문자열내 특정 문자(열)을 다른 문자(열)로 변경하기(예시: 이름,주민등록번호)
엑셀 substitute, relace 함수로 문자열내 특정 문자(열)을 다른 문자(열)로 변경하기(예시: 이름,주민등록번호)
엑셀의 문자 변경 함수에는 substitute 함수와 REPLACE가 있습니다.
substitute 함수는 문자 단위로 처리하고 REPLACE 함수는 위치 단위로 처리합니다.
데이터의 상황에 따라 적합한 함수를 쓰면 되겠지만 컴활 등 자격증 시험에는 특정 함수를 지정해 결과를 산출하라는 문제가 제출되고 있어 두가지 함수를 사용해 문자(열)을 변경하는 예시를 들어보겠습니다.
함수 구문
=substitute(text,old-text,new-text, [instance])
- text: 변경 대상 문자(문자열)가 있는 문자열
- old-text: 변경 대상 문자(문자열)
- new-text: 변경 후 새로운 문자(문자열)
- instance_num
- 선택 요소
- 변경 전 문자(문자열)이 다수일 경우 보이는 순서에 따른 숫자
- 생략할 경우 해당 바뀌기 전 문자(문자열)이 모두 바뀝니다.
=replace(text,위치,길이,new-text)
- text: 변경 대상 문자(문자열)가 있는 문자열
- 위치: 변경 대상 문자(문자열)가 시작되는 위치
- 길이: 변경 대상 문자(문자열)의문자수
new-text: 변경 후 새로운 문자(문자열)
이름의 2번째 문자를 다른 문자로 변경하는 경우
위 이미지는 B열의 이름 문자열 중 2번째 문자를 문자 "*"로 변경한 결과 예시입니다.
C열은 SUBSTITUTE 함수를 활용한 결과 내용이고
E열은 REPLACE 함수를 활용한 결과 내용입니다.
▶SUBSTITUTE 함수
=SUBSTITUTE(B2,MID(B2,2,1),"*")
MID(B2,2,1): 변경해야할 문자를 지정하기위해 mid 함수를 사용해 이름 문자열(B2)에서 2번째 하나의 문자를 가져옵니다.(SUBSTITUTE 함수는 변경 대상 문자를 지정해야 합니다.)- 변경 후 문자 를 "*"로 지정합니다.
- 위이미지에서 ①은 이름의 두번째와 세번째 문자가 같은 경우이며 ②는 첫번째와 두번째 문자가 같은 경우로 의도한 대로의 결과가 아닙니다. 함수의 마자막 인자 instance_num (SUBSTITUTE(B2,MID(B2,2,1),"*",[instance_num])으로 조정 가능할 것 같으나 instance_num은 위치 지정이 아니고 표시된 순서를 지정하는 것으로 2번째 위치를 지정할 수 없습니다. 따라서 IF 등 조건식 중첩이 필요할 것같습니다.
▶REPLACE 함수
=REPLACE(B2,2,1,"*")
REPLACE함수를 사용하면 간결합니다.- 이름 문자열 B2에서 2번째 위치의 문자를 "*” 문자로 변경합니다.
이름의 중간 문자(열)을 같은 문자 수의 다른 문자(열)로 바꾸는 함수식
위 이미지는 B열의 이름 문자열 중 첫번째와 마지막 문자를 제외한 문자(열)을 같은 문자 수의 문자 로 변경한 예시입니다.
C열은 SUBSTITUTE 함수를 활용한 결과 내용이고
E열은 REPLACE 함수를 활용한 결과 내용입니다.
▶SUBSTITUTE 함수
=SUBSTITUTE(B2,
MID(B2,2,
IF(LEN(B2<=3,1,LEN(B2)-2)),
REPT("*",
IF(LEN(B2)<=3,1,LEN(B2)-2)))
- MID함수로 이름 문자열(B2)이 3자이하인 경우 2번째 한개의 문자를 3자 보다 긴 경우 이름 문자열(B2)의 앞뒤 두 문자를 제외한 문자 수의 문자열을 가져옵니다.(LEN함 수 활용)
- 변경 후의 문자(열)는 문자(열) 길이만큼 REPT함수로 지정합니다.( LEN함 수 활용)
- ①, ②는 위의 경우와 같군요.
주민등록번호 뒷자리 변경
위 이미지의 ①은 주민등록 번호 마지막 7자리를 "*"로 변경한 것이고 ②는 성별 표시 문자를 제외한 마지막 6자리를 "*"로 변경한 것입니다.
C열은 SUBSTITUTE 함수를 활용한 결과 내용이고
E열은 REPLACE 함수를 활용한 결과 내용입니다.
▶SUBSTITUTE 함수
① 주민등록 번호 마지막 7자리 변경=SUBSTITUTE(B2,RIGHT(B2,7),"*******")
RIGHT(B2,7): 이름 문자열에서 마지막 7자리 문자열을 가져옵니다.- RIGHT함수로 가져온 문자열을 "*******"로 변경합니다.
② 주민등록 번호 마지막 6자리 변경
=SUBSTITUTE(B3,RIGHT(B3,6),"******")
RIGHT(B3,6): 이름 문자열에서 마지막 6자리 문자열을 가져옵니다.- RIGHT함수로 가져온 문자열을 "******"로 변경합니다.
▶REPLACE 함수
① 주민등록 번호 마지막 7자리 변경
=REPLACE(B2,8,7,"*******")
- 이름 문자열에서 마지막 7자리 문자열이 시작되는 위치 8의 문자부터 마지막 문자까지 7개의 문자를 "*******"로 변경합니다.
- ② 주민등록 번호 마지막 6자리 변경
=REPLACE(B3,9,6,"******")- 이름 문자열에서 성별 표시 문자를 제외한 마지막 6자리 문자열이 시작되는 위치 9의 문자부터 마지막 문자까지 6개의 문자를 "******"로 변경합니다.