[ 응용 ] 주민등록번호 형식 확인하기, 데이터 유효성 검사

주민등록 번호를 엑셀에 입력할 때 

형식이 맞는지 확인하는 데이터 유효성 검사 함수식입니다.

 

 준비하기

 

아래 그림과 같이 이름과 주민등록번호를 입력해야 할 때

B열에 입력되는 값이 주민등록번호의 형식과 맞지 않으면 제한하는 방법입니다.

 

반응형

 000000-0000000 형태로 입력 제한하기

 

주민등록번호의 형태를 분석해보면

6자리 숫자와 "-" 그리고 7자리 숫자의 결합으로 되어 있습니다.

 

B2셀부터 B13셀까지 이 형식에 맞는 데이터를 입력하게 하려면

아래와 같은 순서로 진행하면 됩니다.

 

1. 데이터 범위 선택

 

2. 데이터 탭 → 데이터 유효성 검사 → 데이터 유효성 검사

 

3. 설정 → 사용자 지정 → 수식에 수식 입력

 

4. 수식 입력 

아래 수식을 입력하면 주민등록 번호의 형태만 입력이 가능하도록 제한할 수 있습니다.

=AND(ISNUMBER(VALUE(LEFT(B2, 6))), MID(B2, 7, 1)="-", ISNUMBER(VALUE(RIGHT(B2, 7))), LEN(B2)=14)

 

728x90

 수 식 해 석

 

먼저 AND() 함수는 입력된 인자의 조건이 모두 참일 경우에 TRUE를 반환합니다.

위 함수식에서 입력된 인자는 모두 세 개입니다.

1. ISNUMBER(VALUE(LEFT(B2, 6)))

2. MID(B2, 7, 1)="-"

3. ISNUMBER(VALUE(RIGHT(B2, 7)))

4. LEN(B2)=14

 

LEFT() 함수를 통해 주민등록번호의 앞에 6자리 데이터를 가지고입니다.

MID() 함수를 통해 7번째 글자가 "-"가 맞는지 확인합니다.

RIGHT() 함수를 통해 뒤에 7자리 데이터를 가지고 옵니다.

 

그다음 VALUE() 함수를 통해

LEFT() 함수와 RIGHT() 함수로 가지고 온 데이터를 숫자로 바꾸어줍니다.

만약 가지고 온 데이터가 텍스트 형태의 숫자라면 숫자 형태로 바꾸어 주고

단순 텍스트라면 에러가 발생합니다.

 

ISNUMBER() 함수는 입력된 데이터가 숫자가 맞는지 확인합니다.

숫자가 맞다면 TRUE

숫자가 아니라면 FALSE

를 반환합니다.

 

마지막으로 LEN() 함수를 사용해 입력한 글자수가 모두 14개 인지 확인을 합니다.

 

이 조건을 모두 충족해야 입력이 가능해집니다.

 

  마 치 며

 

주민등록번호의 구성을 논리적으로 조금 더 자세히 구분할 수 있습니다만,

엑셀에서 사용할 수는 없습니다.

 

예를 들면

6자리 숫자 중 

맨 앞 두 자리는 00~99까지,

그다음 두 자리는 01~12까지

마지막 두자리는 01~31까지 

로 제한할 수 있도록 수식을 작성할 수는 있지만,

데이터 유효성 검사의 수식을 입력하는 곳에 수식이 입력이 되지 않습니다.

글자수 제한이 있기 때문이고 그 제한 숫자는 255글자인 것으로 알고 있습니다.

여기를 방문하시면 더 많은 엑셀 관련 자료를 확인할 수 있습니다.

엑셀 공부하기
VBA 공부하기

 

=and(isnumber(value(left(b2, 6))), mid(b2, 7, 1)="-", isnumber(value(right(b2, 7))), len(b2)=14)