|
오늘은 엑셀 묻고 답하기 게시판에 [ryujoo]님의 질문에 대해 [khan..]님이 답변한 내용을 소개합니다.
(엑셀 묻고 답하기 게시물 번호: 46251, 사용버전: Excel 2003)
[질문]주민등록번호 DATA의 숫자들 중 뒷자리 6개의 숫자를 ‘*’으로 표시 할 수 있나요?
ex) 123456-1234567를 123456-1****** 으로 표시하려고 합니다.
또, 한 셀에 두 개의 주민등록번호가 있을 경우도 있습니다. 이럴 경우에도 적용 가능한 방법 좀 부탁 드립니다.
[답변]IF, LEN, REPLACE, SEARCH 함수를 이용하여 다음과 같은 수식을 사용합니다.
=IF(LEN(B2)>15,REPLACE(REPLACE(B2,SEARCH("-",B2)+2,6,"******"),SEARCH("-",B2,8)+2,6,"******"),REPLACE(B2,SEARCH("-",B2)+2,6,"******")) |
|
|
|
|
아래와 같이 가상의 주민등록번호가 입력되어 있다고 가정하겠습니다.
<1> 한 셀에 입력된 한 개 또는 그 이상의 주민등록번호의 뒷자리 6개의 숫자를 ‘*’기로 대신 표시하기 위해서 B1셀에 다음과 같은 수식을 입력합니다.
| =IF(LEN(A1)>15,REPLACE(REPLACE(A1,SEARCH("-",A1)+2,6,"******"),SEARCH("-",A1,8)+2,6,"******"),REPLACE(A1,SEARCH("-",A1)+2,6,"******")) |
<2> B1셀에 다음과 같이 결과가 표시됩니다.
<3> B1셀의 수식을 자동 채우기 기능을 이용하여 B5셀까지 복사합니다.
[풀이]
LEN함수를 이용하여 한 셀에 주민등록번호가 1개가 입력되어 있는지, 2개가 입력되어 있는지 구분합니다.
입력된 데이터의 길이가 15자리 이하인 경우에는 REPLACE 함수를 한번 이용하여 뒷자리 6개의 숫자를 ‘*’로 바꿔주고, 15자리 이상인 경우에는 REPLACE 함수를 2번 이용하여 작업을 두 번 수행합니다.
이때 SEARCH함수를 살펴보면 SEARCH("-",A1,8)+2,6,"******")라고 하여 8번째 이후에 입력된 ‘-’의 위치를 반환하도록 되어 있습니다.
[참고] 함수
| 함수명 |
설 명 |
| REPLACE |
지정한 문자 수에 따라 문자열의 일부를 다른 문자열로 바꿉니다. |
[형식] REPLACE(old_text,start_num,num_chars,new_text)
Old_text: 바꾸려는 문자열입니다.
Start_num: old_text에서 바꿀 문자의 위치입니다.
Num_chars: REPLACE가 old_text에서 new_text로 바꿀 문자열의 수입니다.
New_text: old_text에 바꾸어 넣을 새 문자열입니다 |
[예] REPLACE(A1,2,6,"******")
입력: A1=> 1111111
결과: 1******
해설: A1셀에 입력된 데이터의 2번째 자리부터 6개를 ‘*’로 바꿔줍니다. |
| SEARCH |
start_num부터 시작하여 특정 문자 또는 텍스트 문자열이 처음 발견되는 문자의 위치를 반환합니다 |
[형식] SEARCH(find_text,within_text,start_num)
Find_text: 찾고자 하는 텍스트입니다. find_text에 와일드카드 문자인 물음표(?)와 별표(*)를 사용할 수 있습니다. 물음표는 한 문자에 해당하고 별표는 일련의 문자에 해당합니다. 실제 물음표나 별표를 찾으려면 문자 앞에 물결표(~)를 입력합니다.
within_text: find_text를 찾으려는 텍스트입니다.
start_num: within_text에서 찾기 시작할 문자의 위치입니다. |
[예] SEARCH("-",A1)
입력: A1셀=> 123456-1234567
결과: 7
해설: A1셀에 입력된 데이터에서 ‘-’위치를 알려줍니다. |
| LEN |
문자열의 문자 수를 구합니다 |
[형식] LEN(text)
Text: 길이를 알려는 문자열로서 공백도 문자로 계산됩니다. |
[예] LEN(A1)
입력: A1셀=> 123456-1234567
결과: 14
해설: A1셀에 입력된 문자열의 문자 수를 계산하여 줍니다. | | |
출처 : 오피스 튜터 |
