엑셀 수식으로 주민등록번호에서 생년월일을 날짜 형식으로 추출 하는 방법

엑셀에서 회원 명부, 직원 명부 또는 기타 다양한 목적으로 이름과 주민등록번호, 연락처 등을 시트에서 관리하게 됩니다.

가끔 주민등록번호에 생년월일에 해당하는 날짜를 추출하고자 할 때가 있습니다.
문자열을 나누는 MID 함수를 사용하여 간단하게 날짜 형식으로 표시할 수 있지만 MID 함수만 사용해서 년-월-일 형식으로 만들 경우 정확한 년도를 표시할 수 없는 문제점이 발생하게 됩니다. 1900년대인지, 2000년대인지 정확한 구분을 해주어야 합니다.

이번 글에서는 엑셀 수식으로 주민등록번호에서 생년월일을 날짜 형식으로 추출 하는 방법에 대해 알아보겠습니다.

엑셀에서 지정 위치의 문자열을 가져오는 MID 함수 알아보기

주민등록번호에서 날짜 형식의 년-월-일을 추출하기 위해서는 MID 함수에 대해 먼저 알아야 합니다.
MID 함수는 문자열의 지정 위치에서 문자를 지정한 개수만큼 가져오는 함수입니다.

• 기본 구문 : MID(text, start_num, num_chars)
• text : 지정 위치에서 문자를 가져올 문자들이 포함된 문자열입니다.
• start_num : 가져올 문자열에서 첫째 문자의 위치입니다. Text에서 첫 문자는 1이 됩니다.
• num_chars : Text에서 가져올 문자 개수를 지정합니다.
• 사용 예시 : MID(“20240117”, 5, 2) 했을 때, “01″를 가져옵니다.
  문자열 “20240117”에서 5번째 위치에서 2개의 문자를 가져 옵니다.

엑셀 수식으로 주민번호에서 생년월일 날짜 추출하기

앞 단락에서 문자열을 가져오는 MID 함수에 대해서 알아보았으니 이제 MID 함수를 기본적으로 활용하여 주민등록번호에서 생년월일을 가져오는 수식을 구현해 보겠습니다.

1. 아래 시트를 보면 B열에 주민등록번호가 작성되어 있습니다.

2. 단순히 MID 함수만 이용해서 주민등록번호에서 년-월-일 형태의 텍스트 문자열을 만들어 보겠습니다.

3. 수식 : =MID(B2,1,2) & “-” & MID(B2,3,2) & “-” & MID(B2,5,2)
설명 : B2 셀의 주민번호에서 첫 번째와 두 번째, 세 번째와 네 번째, 다섯 번째와 여섯 번째로 각각 세 번 문자열을 나누어 가져오고 년월일 사이에 “-“를 추가해 주었습니다.

4. MID로 년-월-일 형태로 만들었지만 아직 텍스트 문자열입니다. DATE(날짜) 포맷으로 만들기 위해 DATE 함수를 추가로 사용해 보겠습니다.

• DATE 함수의 기본 구문 : DATE(year, month, day)

5 수식 : =DATE(MID(B2,1,2), MID(B2,3,2), MID(B2,5,2))

6. DATE 함수를 사용하여 Year, Month, Day에 해당하는 각각의 인수에 MID 함수로 가져온 문자열을 넣고 날짜 포맷으로 변환했을 때 아래 화면의 시트처럼 만들어지게 됩니다.

7. 그런데 년도가 표시되는 부분에서 오류가 발생하는 것을 볼 수 있습니다. 주민등록번호의 앞 두 자리 년도에서 1900년대와 2000년대를 구분하는 것은 7번째 자리의 숫자 값에 따라 결정되기 때문에 2000년대를 정확히 처리하지 못하는 문제가 발생하게 됩니다.

8. 주민등록번호 7번째의 성별과 연도 구분 값을 사용하게 정확한 년도를 가져오도록 수식을 변경해 보겠습니다.

9. 수식 : =DATE(IF(OR(MID(B2,8,1)=”1″, MID(B2,8,1)=”2″), “19”, “20”) & MID(B2,1,2), MID(B2,3,2), MID(B2,5,2))

• OR(MID(B2,8,1)=”1″, MID(B2,8,1)=”2″) : 주민등록번호 7번째 자리가 “1”이거나 “2”인 경우 “참(True)”이 됩니다.
  MID함수의 Start_num을 “8”로 해준 이유는 B열의 주민등록번호에서 7번째 자리에 “-“이 들어있기 때문입니다.
• IF(OR(MID(B2,8,1)=”1″, MID(B2,8,1)=”2″), “19”, “20”) : OR 함수 결과 값이 “참(true)”인 경우 “19”를 표시하고, 그렇지 않은 경우 “20”을 표시하도록 해 주었습니다.
• 외국인 주민등록번호까지 고려한다면 수식은 =DATE(IF(OR(MID(B2,8,1)=”1″, MID(B2,8,1)=”2″, MID(B2,8,1)=”5″, MID(B2,8,1)=”6″), “19”, “20”) & MID(B2,1,2), MID(B2,3,2), MID(B2,5,2))처럼 해 주면 됩니다.
• 엑셀에서 IF 함수와 OR 함수에 대해 좀 더 자세히 알고 싶다면 아래 다른 포스팅 링크를 참고하시면 됩니다.

주민등록번호에서 성별과 연도를 구분하는 방

▶ 주민등록번호에서 성별과 연도를 구분하는 것은 7번째 자리의 숫자 값에 따라 구분됩니다.

주민등록번호 7번째 자리 숫자	성별	출생년대
1	남자	1900년대
2	여자	1900년대
3	남자	2000년대
4	여자	2000년대
5	외국인 남자	1900년대
6	외국인 여자	1900년대
7	외국인 남자	2000년대
8	외국인 여자	2000년대

▶ 만약 주민등록번호에서 성별을 가져와야 한다면 어떻게 하면 될까요?

• 수식 : =IF(OR(MID(B2,8,1)=”1″, MID(B2,8,1)=”3″, MID(B2,8,1)=”5″, MID(B2,8,1)=”7″), “남자”, “여자”)
• 7번째 자리의 숫자가 “1”, “3”, “5”, “7”인 경우 “남자”, 그렇지 않으면 “여자”로 가져오면 됩니다.
• 주민등록번호 사이에 “-“가 없다면 수식은 =IF(OR(MID(B2,7,1)=”1″, MID(B2,7,1)=”3″, MID(B2,7,1)=”5″, MID(B2,7,1)=”7″), “남자”, “여자”)으로 해 주면 됩니다.

마무리

주민등록번호에는 많은 정보가 포함되어져 있습니다. 주민번호에서 생년월일을 가져오거나 남,여의 성별을 가져오거나 내/외국인 구분을 가져올 수도 있습니다.

엑셀에서 적절한 수식으로 주민등록번호에서 다양한 정보를 활용해 보시기 바랍니다.

이 글이 도움이 되었기를 바랍니다. ^-^