OfficeTutor 365

Q&A>

오늘은 엑셀 묻고 답하기 게시판에 [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,"******"))

STEP> 따라하기

아래와 같이 가상의 주민등록번호가 입력되어 있다고 가정하겠습니다. <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셀에 입력된 문자열의 문자 수를 계산하여 줍니다.