[엑셀] 주민등록번호에서 생년월일(yyyy-mm-dd) 추출하는 방법

주민등록번호의 앞 6자리는 생년월일을 의미하고 있지만 엑셀에서 주민등록번호 자체는 날짜의 형식이 아니기 때문에 주민등록번호에서 yyyy-mm-dd 형식의 날짜로 추출해야 할 때가 있습니다.

 

이럴 때 사용되는 함수가 IF 함수, LEFT, MID, RIGHT와 같은 텍스트 함수, 그리고 DATE 함수입니다. IF 함수와 텍스트 함수의 사용에 익숙하지 않으신 분들은 아래의 포스팅을 참고해 주시면 됩니다.

 

[엑셀] IF 함수 기초 및 중첩 적용하는 방법

 

[엑셀] LEFT, RIGHT, MID 함수로 문자의 일부를 추출하기

 

이 외에 사용되는 함수가 DATE 함수인데 함수의 구조는 아래와 같습니다.

 

=DATE(Year, Month, Day)

 

함수의 구조는 간단합니다. 첫 번째 인수로 연도에 해당하는 숫자를(1900~9999), 두 번째 인수로 월에 해당하는 숫자를(1~12), 세 번째 인수로 일에 해당하는 숫자를(1~31) 입력하면 날짜가 만들어집니다.

 

주민등록번호는 '700228-1가가가가가가'와 같이 구성되어 있는데 생년월일을 결정짓는 수는 앞 8자리입니다.(- 포함)

 

앞 8번째의 수가 1 또는 2일 경우에는 주민등록번호 앞 2자리에 1900을 더하면 태어난 연도가 만들어지고 3 또는 4일 경우에는 앞 2자리에 2000을 더하면 태어난 연도가 만들어집니다.(외국인일 경우 5,6과 7,8로 구분)

 

여기에 3번째부터 2자리, 5번째부터 2자리를 각각 추출하여 DATE 함수의 인수로 입력하면 생년월일을 만들 수 있습니다.

 

A2:A5셀에 있는 주민등록번호를 참조하여 B2:B5셀에 날짜 형식의 생년월일을 만들어 보겠습니다.

 

아래에서 설명드리는 수식은 내국인 한정으로 적용되는 수식이며, 생년월일을 추출하고자 하는 명단에 외국인이 포함되어 있다면 수식이 조금 달라집니다. 이에 대해서는 글 후반에 다시 설명드리겠습니다.

 '=IF(OR(MID(A2,8,1)="1",MID(A2,8,1)="2"),DATE(1900+LEFT(A2,2),MID(A2,3,2),MID(A2,5,2)),DATE(2000+LEFT(A2,2),MID(A2,3,2),MID(A2,5,2)))'을 입력합니다.

 

(날짜 형식으로 나오지 않는 경우 셀서식에서 날짜로 바꿔줍니다.)

 

수식이 다소 복잡해 보일 수 있습니다. 일단 주민등록번호 8번째(- 포함)의 수가 무엇인지에 따라 값을 다르게 구해야 하므로 제일 먼저 쓰인 함수는 IF 함수입니다. IF 함수는 '조건, 조건이 참일 때 값, 조건이 거짓일 때 값'으로 구성되는 각각의 인수를 하나씩 보겠습니다.

 

IF의 첫 번째 인수 OR(MID(A2,8,1)="1",MID(A2,8,1)="2")입니다. 주민등록번호 앞 8번째 자리가 1 또는 2일 때를 조건으로 만들기 위해 OR 함수를 사용하였습니다. A2셀의 8번째 1개의 수를 추출하여 1 또는 2인 경우 참인 값을 가져옵니다.

 

IF의 두 번째 인수 DATE(1900+LEFT(A2,2),MID(A2,3,2),MID(A2,5,2))입니다. 첫 번째 인수가 참일 때 가져올 값입니다. 결국 만들어야 하는 값이 날짜이므로 DATE 함수를 사용하는데 조건이 참일 때, 즉 주민등록번호 앞 8번째 자리가 1 또는 2일 때는 주민등록번호의 왼쪽 2자리에 1900을 더한 수를 연도로 지정합니다. 그다음 주민등록번호의 앞 3번째 2개 자리를 월로, 주민등록번호의 앞 5번째 2개 자리를 일로 지정합니다.

 

IF의 세 번째 인수 DATE(2000+LEFT(A2,2),MID(A2,3,2),MID(A2,5,2))입니다. 두 번째 인수와 구조는 동일합니다. 다만 첫 번째 인수인 조건이 거짓일 때, 즉 주민등록번호 앞 8번째 자리가 1 또는 2가 아닐 때(3 또는 4일 때) 가져오는 값이므로 주민등록번호 왼쪽 2자리에 2000을 더해서 연도로 지정하는 것이 차이점입니다.

 

수식이 입력된 B2셀을 드래그하여 B5셀까지 채우면 주민등록번호에서 생년월일을 추출할 수 있습니다.

 

앞서 설명드렸듯이 위에서 설명드린 수식은 내국인에게만 적용할 수 있는 수식입니다. 내국인은 주민등록번호 앞 8번째 자리(- 포함)가 1 또는 2인지, 아니면 3 또는 4인지에 따라 1900년대생과 2000년대생을 구분하듯이 외국인은 외국인등록번호 앞 8번째 자리(-포함)가 5 또는 6인지, 7 또는 8인지에 따라 구분됩니다. 명단에 외국인이 포함되어 있다면 수식에 조건을 추가해야 합니다.

 

내국인에게 사용하는 수식을 그대로 사용하면 외국인등록번호에서는 생년월일이 바르게 추출되지 않는 경우가 있습니다. 이럴 때는 수식에 조건을 추가해야 합니다.

 

IF 함수의 두 번째 인수와 세 번째 함수는 그대로 두고 첫 번째 인수를 OR(MID(A2,8,1)="1",MID(A2,8,1)="2")에서 OR(MID(A7,8,1)="1",MID(A7,8,1)="2",MID(A7,8,1)="5",MID(A7,8,1)="6")로 바꿨습니다.

 

즉 내국인만 있을 때는 주민등록번호 앞 8번째 수(-포함)가 '1 또는 2'인지에 대해서만 조건을 만들었는데 명단에 외국인등록번호가 있을 때는 '1 또는 2 또는 5 또는 6'으로 조건을 추가시킨 겁니다.